How combined data from multiple sheets to one sheet.

I have a document with 7 sheets with data. Each have the same 4 column headers and each vary in the number of rows from as many as 2500 on one and only 5 on another.

What I want to do it have all of the data combined onto one tab. So I would see sheet 1 from row 1-2500 and the next sheet would start below that at row 2501.

I cannot have tables as when the file is exported the tables disappear.

Thanks in advance for your help.

excel

Formulas and Functions

HansVogelaar

Feb 04, 2021

ccate

Try this macro:

Sub CombineSheets()
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Dim i As Long
    Dim t As Long
    Dim rng As Range
    Application.ScreenUpdating = False
    Set wshT = Worksheets.Add(Before:=Worksheets(1))
    t = 1
    For i = 2 To Worksheets.Count
        Set wshS = Worksheets(i)
        Set rng = wshS.UsedRange
        If i > 2 Then
            Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
        End If
        rng.Copy Destination:=wshT.Range("A" & t)
        t = t + rng.Rows.Count
    Next i
    Application.ScreenUpdating = True
End Sub

6 Replies