I am using the below code to copy data from each sheet onto a summary sheet, but, I have two problems.
1. Some of the sheets do not have any data in column B [except the header] which means that when the next sheet copies the data onto the summary sheet it overwrites the the data where there is nothing in 'B'
2. Each sheet has a header but I only want to copy the header from the first sheet onto the summary sheet.
Can an expert help me out with this so that all the sheets are copied onto the summary sheet and nothing gets overwritten.
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name <> "Summary" Then
ActiveSheet.Paste Range("B65536").End(xlUp).Offset(1, 0)
Application.ScreenUpdating = True