VBA: Copy cell in worksheet1 to worksheet2 based on value of cell

Question

Using VBA, how do I:

Copy cell B1 in Worksheet1 to a new Worksheet2 only if cell A1 (in Worksheet1) value = "YES".

This then repeats for each row in range A1:A1000 (i.e. B2 copies based on A2 value = "YES" and so on) - if cell is blank, check next row.

Sub Output()
Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet

    Set Source = ActiveWorkbook.Worksheets("worksheet1")
    Set Target = ActiveWorkbook.Worksheets("worksheet2")

    j = 1     ' Start copying to row 1 in target sheet
    For Each c In Source.Range("R2:R1000")   ' Do 1000 rows
        If c = "YES" Then
           Source.Rows(c.Row).Copy Target.Rows(j) /*getting stuck here on trying to copy a cell from column S in worksheet1 to worksheet2
           j = j + 1
        End If
    Next c
End Sub

What have you tried? This isn't a 'Code for Me' site, but we will help you with some errors. See How to Ask for more information. Also, a quick search on this site alone yields Bunches of Results that may help you. — PartyHatPanda
Sorry - trying to edit post to show code properly and I'm having user error. — Nati
For the record, I copied your code block over to mine and it runs fine. What error are you getting? Also, in your question you are asking to check A1, A2, etc, but in your code you are looking in column R — PartyHatPanda
I had modified what I posted so it does actually run now, the main issue I'm having is that I'd like to only copy one cell from worksheet1 to worksheet2, not the entire row... Thanks for your assistance. I'm a lawyer, not a coder and haven't worked in VBA for years, so I'm a bit rusty and out of my element. Appreciate your patience. — Nati
I used generic designation. I'm specifically checking Column R and want to copy cells from Column AG. — Nati

PartyHatPanda PartyHatPanda · Accepted Answer · 2017-01-27T20:36:30

You just need a quick modification to change from copying the row to the individual cells and location. I used that you are copying from column AG in row c.

Sub Output()
Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet

Set Source = ActiveWorkbook.Worksheets("workshee1")
Set Target = ActiveWorkbook.Worksheets("workshee2")

j = 1     ' Start copying to row 1 in target sheet
    For Each c In Source.Range("R2:R1000")   ' Do 1000 rows
        If c = "YES" Then
            Target.Cells(j, "A").Value = Source.Cells(c.Row, "AG").Value
            j = j + 1
        End If
    Next c
End Sub

VBA: Copy cell in worksheet1 to worksheet2 based on value of cell

1 Answers