I have received great advice here in the past and I greatly appreciate it! I am hoping someone can help me with this issue as well...
I have a workbook with multiple sheets that are being updated regularly by different users. I am using a Sub Summarize macro to copy all data from the individual sheets and paste it all on one summary sheet ("Data Summary") in the workbook so that management doesn't need to look at 20 different sheets to get the data.
The code I am using works great and data from all the sheets populates correctly on the summary sheet but I need some help fine tuning 2 issues. I know it is probably a simple fix but for some reason i just can't figure it out. 
Issue 1: I need to copy data from all sheets starting at Row 3 instead of row 1 because the first 2 rows on all sheets are header rows. Right now when I run the Macro I end up with 20 identical header rows on the "Data Summary" sheet
Issue 2: I need one sheets data to be excluded from the summary as it is an instructions page. The sheet name is "Storm" and I do not want its data included in the Summary sheet.
Here is the code I am using any help would be greatly appreciated!
Sub Summarize()
Dim ws As Worksheet
Dim lastRng As Range
Application.ScreenUpdating = False 'speed up code
ThisWorkbook.Sheets("Data Summary").Rows("2:65536").ClearContents 'clear
For Each ws In ThisWorkbook.Worksheets
Set lastRng = ThisWorkbook.Sheets("Data Summary").Range("A65536").End(xlUp).Offset(1, 0)
Select Case ws.Name
Case "Data Summary" 'exlude
'do nothing
Case Else
ws.Activate
'copy data from individual sheets
Range("A6556", Range("X65536").End(xlUp)).Copy lastRng
End Select
Next
Application.CutCopyMode = False 'clear clipboard
Application.ScreenUpdating = True
Sheets("Data Summary").Activate
End Sub
Bookmarks