Copying rows from multiple worksheets into one worksheet based on criteria

[phily4321]

Hi Everyone,

I'm coming to you with advice on a problem i'm having.

I'm trying to find a way to copy rows based on criteria from multiple worksheets (tower base, sub level, platform 1, platform 2, platform 3, platform 4, platform 5, yaw platform, nacelle, hub & roof) into one worksheet (major non-conformity issues).


This is a screenshot of the worksheet I am trying to get the rows to copy to

This is a screenshot of what is contained on the worksheet's that i want to copy from

the criteria that is needed for the row to be copied will always be found in row F and is 5 out of the 6 possible selections in the dropdown list, with the only one I don't want to be copied containing '1 = serviceable'.

I have been through endless articles, forums and videos and I cannot find the right solution. I am asking here to see if anyone can guide me in the right path to finding a way to do this, if it is possible.

thanks

 

[Fluff]

Hi, welcome to the board.

This needs to go in the ThisWorkbook module. And when col F is changed that row will be copied to the Non-con sheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Sh.Index <= 3 Then Exit Sub
    If Target.CountLarge > 1 And Target.Column <> 6 Then Exit Sub
    If Left(Target.Value, 1) <> 1 Then
        Target.EntireRow.Copy Sheets("List").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If

End Sub

This should work with the layout you've shown above. But if you change the order of the sheets, you may get problems (It currently ignores the first 3 sheets)