OK so I guess I was wrong...The code worked great in the sample workbook attached but for some reason when I add it to the actual workbook I need it for it still includes row 2 from all sheets in the summary but I need rows 1 and 2 excluded as those are header rows. I have attached the actual workbook I need it for if someone could take a look and let me know what I am doing wrong I would greatly appreciate it!
When you open the work book a userform will appear. Check the "fiber data summary" box and that sheet will load. At the top righ of the sheet is the "summary button" that runs the summarize macro. Once you run it you will see all the header rows populate.
The code is under ThisWorkbook in VBA.
Sub Summarize()
Dim ws As Worksheet
Dim lastRng As Range, lr As Long
Application.ScreenUpdating = False 'speed up code
ThisWorkbook.Sheets("Data Summary").Rows("3:65536").ClearContents 'clear
For Each ws In ThisWorkbook.Worksheets
If InStr(ws.Name, "Instructions") = 0 And InStr(ws.Name, "Summary") = 0 Then
Set lastRng = ThisWorkbook.Sheets("Data Summary").Range("A65536").End(xlUp).Offset(2, 0)
lr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
ws.Range("a3:x" & lr).Copy lastRng
End If
Next
Application.CutCopyMode = False 'clear clipboard
Application.ScreenUpdating = True
Sheets("Data Summary").Activate
End Sub
Bookmarks