Copy data in one column to a different column on another sheet based on a third column's data

Question

So, I'm very new to VBA and I am having a difficult time finding answers to what I believe should be a fairly straightforward question.

I have a workbook that has 2 sheets, which we will call Sheet1 and Sheet2.

I want to copy data from columns B, D and E on Sheet1 to the first available row in columns A, B and C on Sheet 2, with the following changes:

Sheet1 Sheet2
Col E Col A
Col D Col B
Col B Col C

But I only want the data to be copied if the cell value of each row in Sheet1 Column I is "Y".

I don't have any code for this yet.

UPDATE: After taking advice from a response, I ran a Macro record and got this:

    Sub VBlk()
    '
    ' VBlk Macro
    ' V Block Copy
    '

    '
        Range("B2").Select
        Selection.Copy
        Sheets("Sheet2").Select
        Range("C3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
SkipBlanks _
            :=False, Transpose:=False
        Range("B3").Select
        Sheets("Sheet1").Select
        Range("D2").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("E2").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("A3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End Sub

By "But I only want the data to be copied if the value of Sheet1 Column I is Y" you mean the whole column or a cell value in column I? — warner_sc
Cell value for each row. So if $I$4 = "Y", then I want to copy $B$4, $D$4 and $E$4 to the first open row on Sheet 2. — Justin F
You write "yet". Why not wait until you have some code and post it in the spirit of this place? Use the macro recorder, Google, there's plenty out there to get you started. — SJR

LatifaShi LatifaShi · Accepted Answer · 2018-04-06T15:04:56

Try the code below (Hope it helps) :

   Sub test()

        Dim ws1 As Worksheet
        Dim ws2 As Worksheet
        Set ws1 = Sheets("sheet1")
        Set ws2 = Sheets("sheet2")

        'get the Last non empty row in sheet1 based on Column B
        lastrow1 = ws1.Cells(Rows.Count, 2).End(xlUp).Row


        For i = 1 To lastrow1
            'get the Last non empty row in sheet2 based on Column A
            lastrow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
            If ws1.Range("I" & i).Value = "Y" Then
                ws2.Range("A" & lastrow2 + 1).Value = ws1.Range("E" & i)
                ws2.Range("B" & lastrow2 + 1).Value = ws1.Range("D" & i)
                ws2.Range("C" & lastrow2 + 1).Value = ws1.Range("B" & i)
            End If
        Next i

    End Sub

Copy data in one column to a different column on another sheet based on a third column's data

1 Answers