For the code below to work I assumed the following:
- There is data in cell A1 and the rest of the data is connected to it. By which I mean that there is data to the cell next to the cell with data in it. When you have data in range A1:A10 and C1:C10, then column C won't get copied.
- The total amount of data rows doesn't exceed the maximum number of rows of a single sheet.
Altering the code:
- I named the sheet where all the data is copied to "Master". Change this to match your destination sheet name on code line 5.
- When you want an empty row between the data from each sheet then change Offset(1, 0) into Offset(2, 0) on code line 11.
Give the code a try and see if it yields the desired result.
Dim sh As Worksheet
Dim dSheet As String
dSheet = "Master"
For Each sh In Worksheets
If Not sh.Name = dSheet Then
Sheets(dSheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)