VBA Macro to copy and paste certian cells to another sheet based on another cell value

[Steves73]

hello

I am very new to VBA, I have been searching for weeks now for a easy macro to copy values from certain cells and paste them to another sheet based on another cell value

Example - Macro to search column "O" if it finds the word "Reorder" it copies cells A1, A2, A4, and paste the values to Sheet 3, starting in column A. The paste finds the next blank cell.

 

[Joe4]

OK, you can explicitly define all the sheet names you want to apply it to at the beginning of the code in an array. Then you can loop through this array.
Here is what that structure would look like.

Sub Gen_Active()

    Dim shts As Variant
    Dim s As Long
    Dim lastRow As Long
    Dim myRow As Long
    Dim myCopyRow As Long
    Dim ws As Worksheet

'   Set initial row to copy to as 4
    myCopyRow = 4
    
'   Define all the sheets this should run on
    shts = Array("Prep", "Year 1", "Year 2", "Year 3", "Year 4", "Year 5", "Year 6")

    Application.ScreenUpdating = False

'   Loop through all sheets
    For s = LBound(shts) To UBound(shts)
        Set ws = Sheets(shts(s))
'       Find last row with data in column Z on sheet
        lastRow = ws.Cells(Rows.Count, "Z").End(xlUp).Row
'       Loop through all rows if row Z is set to Active
        For myRow = 3 To lastRow
            If ws.Cells(myRow, "Z") = "Active" Then
                Sheets("2018 Active").Cells(myRow, "A") = ws.Cells(myCopyRow, "A")
                Sheets("2018 Active").Cells(myRow, "B") = ws.Cells(myCopyRow, "B")
                Sheets("2018 Active").Cells(myRow, "Y") = ws.Cells(myCopyRow, "C")
                Sheets("2018 Active").Cells(myRow, "Z") = ws.Cells(myCopyRow, "D")
                Sheets("2018 Active").Cells(myRow, "AA") = ws.Cells(myCopyRow, "E")
                Sheets("2018 Active").Cells(myRow, "AB") = ws.Cells(myCopyRow, "F")
                Sheets("2018 Active").Cells(myRow, "AC") = ws.Cells(myCopyRow, "G")
                Sheets("2018 Active").Cells(myRow, "AD") = ws.Cells(myCopyRow, "H")
                Sheets("2018 Active").Cells(myRow, "AE") = ws.Cells(myCopyRow, "I")
'               Increment row counter
                myCopyRow = myCopyRow + 1
            End If
        Next myRow
    Next s

    Application.ScreenUpdating = True

End Sub

 

[Grub88]

Thanks for the code. Something seems to be not working as it is bringing across a mix of entries (i.e. not only ones that have 'active' but also some 'inactive' and 'monitor'. It's also bringing only a handful of entries and putting lots of spaces between them.
I've ended up writing a macro for Year 1 that prints info across starting at line 4, and then a separate macro for the other years starting at the next available empty row.
I then created a macro to run all the other macro's in order (including a clear content to start with so if/when I update it will just keep adding below all the time).
The work around has met my needs for now so we can get things up and running. Might come back to it for more concise coding in the future.
Thank you so much for all your input!!