How to copy specific cells from each row in another sheet if certain condition is met?

Question

So my problem is this. I have a workbook with lets say 2 sheets. I have automatically created sheet2 from another program and sheet1 where I would like only some of the information from sheet2.

I am now trying to create a macro that would check each row starting from 14 with the value in E% greater than 15. If the condition is met I would like the macro to copy cell value from C% and E% to sheet1 lets say in A5 and B5 and then proceed to next row in sheet2 pasting the valued to A6 B6 and so on.

Sub Test()
    Dim rng As Range
    Dim lastRow As Long
    Dim cell As Variant


    With Sheets("Sheet2")


        lastRow = .Range("E" & .Rows.Count).End(xlUp).Row
        Set rng = .Range("E14:E" & lastRow)


        For Each cell In rng
            If cell.Value > 15 Then
            'And here is where it gets bugged. I know theres something wrong with the .select but I couldnt think of any other way to
            'pick only just the 2 cells needed.
                Range(cell.Offset(0, -1), cell.Offset(0, 0)).Select
                Selection.Copy

                'In here there should also be some code to select where to place the copyed
                'data but since it already got bugged couldnt really find a solution for 
                'it..
                Sheets("Sheet1").Select
                ActiveSheet.Paste
                Sheets("Sheet2").Select

            End If
        Next
    End With


End Sub

sooo where are you with that? what have you tried and what errors are you running into? Nobody is going to code your macro for you, but post your code and tell us what errors you're seeing and we'll help you through — user1759942
hmm so what seems to happen? When i run the code it doesnt really fail at all. is it getting stuck when the cells are blank? or when they are text instead of numbers? I pasted various numbers in column e in rows 14 through 28 and it looped through all of them. cell was set properly, it selected cell in column e and the one to the left of that (column d, if you want to select to the right, use positive numbers in the offset) it copied the selection, it activated sheet1 (although i'd use .activate instead) and it pasted, then went back to sheet2 — user1759942
rather than select / copy, you could try Range(cell.Offset(0, -1), cell.Offset(0, 0)).copy and when pasting, you'd want to actually have it select where to paste, so after activating the target sheet, you'd wanna have like Range("a5", "b5").pasteSpecial. you'd use PasteSpecial as range object do not support the paste method, and activesheet.paste only really works if you're just pasting in like A1 or something. — user1759942

user1759942 user1759942 · Accepted Answer · 2014-02-18T15:55:07

so I guess i'll put it together:

Sub Test()
Dim rng As Range
Dim lastRow As Long
Dim cell As Variant
dim count as long
count = 0

With Sheets("Sheet2")


    lastRow = .Range("E" & .Rows.Count).End(xlUp).Row
    Set rng = .Range("E14:E" & lastRow)


    For Each cell In rng
        If cell.Value > 15 Then
        'And here is where it gets bugged. I know theres something wrong with the         .select but I couldnt think of any other way to
        'pick only just the 2 cells needed.
            Range(cell.Offset(0, -1), cell.Offset(0, 0)).Select
            Selection.Copy
        'maybe use: Range(cell.Offset(0, -1), cell.Offset(0, 0)).copy


            'In here there should also be some code to select where to place the copyed
            'data but since it already got bugged couldnt really find a solution for 
            'it..
            Sheets("Sheet1").Activate
            Range("A5", B5).offset(count, 0).PasteSpecial 'this will make it so that it starts in a5, and moves down a row each time
            count = count + 1            'dont forget to increment count

            Sheets("Sheet2").Activate

        End If
    Next
End With


End Sub

and that's kinda a rough thing..

you might include some error handling like: if not cell.value = "" then or also if not isNumeric(cell.value) then and those together would ensure you're only processing non blank cells with numbers.

How to copy specific cells from each row in another sheet if certain condition is met?

1 Answers