How do I fix the below Macro to make it copy all rows that has value to the new sheet? and only want the header copy once. Thanks.
Public Sub Collate_Sheets()
Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim sh As Worksheet
Sheets.Add After:=Sheets(Sheets.Count)
Set wks = Sheets(Sheets.Count)
wks.Name = "GoTax Import"
FirstRow = 1
LastRow2 = 1
For Each sh In Worksheets
If Right(sh.Name, 11) = "Final Scrub" Then
With sh
LastRow1 = .Range("B" & .Rows.Count).End(xlUp).Row
.Range(.Cells(FirstRow, "A"), .Cells(LastRow1, "V")).Copy wks.Range("A" & LastRow2).End(xlUp)
FirstRow = 2
LastRow2 = LastRow1 + LastRow2
End With
End Sub
Bookmarks