Combine data of multiple worksheets on single sheet

[prince]

Hello Sir, I have stored the data of multiple worksheets that I want to store on the single worksheet without copy-paste. The Rows & columns are not fixed on each worksheet. I want VBA code to add all records of each worksheet on a single sheet.
Thanks & regards,
Prince

[HansV]

Will the sheets have one or more header rows? If so, how many?

[prince]

Each worksheet has three rows of header. I want only 3rd row of header on (combined) worksheet

[HansV]

Here is a macro:

Code: Select all

Sub CombineSheets()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim f As Boolean
    Dim t As Long
    Dim rng As Range
    Application.ScreenUpdating = False
    On Error Resume Next
    Set wt = Worksheets("Summary")
    On Error GoTo ErrHandler
    If wt Is Nothing Then
        Set wt = Worksheets.Add(Before:=Worksheets(1))
        wt.Name = "Summary"
    Else
        wt.Cells.Clear
    End If
    f = True
    For Each ws In Worksheets
        Select Case ws.Name
            Case "Summary" ' You can add other sheet names, separated by commas
                ' Skip sheet
            Case Else
                ' Is this the first sheet we copy?
                If f Then
                    ' If so, copy the third header row
                    wt.Range("A1").EntireRow.Value = ws.Range("A3").EntireRow.Value
                    f = False
                End If
                t = wt.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                Set rng = ws.UsedRange
                Set rng = rng.Offset(3).Resize(rng.Rows.Count - 3)
                wt.Range("A" & t).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
        End Select
    Next ws
ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

[PJ_in_FL]

Hans,

Thank you! I was starting on an organizing project for all the external drives I have to manage, and was thinking I'd need something along these lines, too.

[prince]

Thank you sir, it's working as per my requirements.
Thanks a lot sir.

With warm regards
Prince