Sub With_A_Loop()
Dim c As Range, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets("All_Data")
Set sh2 = Worksheets("5")
For Each c In sh1.Range("CU2:CU" & sh1.Cells(Rows.Count, 99).End(xlUp).Row)
If CInt(c.Value) = CInt(sh1.Range("CV1").Value) Then c.Offset(, -98).Resize(, 97).Copy sh2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next c
End Sub
Works on your attachment. As mentioned previously, you always have to change references if not all info is available.
Part of the fives ("5") are numerical while others are text.
The right aligned are numbers while the left aligned are text.
However, like kaasplankje mentioned, filtering is the best way. Fixing the text/numerical makes things easier.
I am sure he/she will be showing up soon to give you a suggestion.
And this works also. Probably as fast or faster then filtering.
Sub With_A_Loop_2()
Dim c As Range, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets("All_Data")
Set sh2 = Worksheets("5")
For Each c In sh1.Range("CU2:CU" & sh1.Cells(Rows.Count, 99).End(xlUp).Row)
If CInt(c.Value) = CInt(sh1.Range("CV1").Value) Then sh2.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 97).Value = c.Offset(, -98).Resize(, 97).Value
Next c
End Sub
Bookmarks