Combine data of multiple worksheets on single sheet

prince
2StarLounger
Posts: 153
Joined: 02 Mar 2015, 17:00

Combine data of multiple worksheets on single sheet

Post by 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

User avatar
HansV
Administrator
Posts: 72196
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combine data of multiple worksheets on single sheet

Post by HansV »

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

prince
2StarLounger
Posts: 153
Joined: 02 Mar 2015, 17:00

Re: Combine data of multiple worksheets on single sheet

Post by prince »

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

User avatar
HansV
Administrator
Posts: 72196
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Combine data of multiple worksheets on single sheet

Post by 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
Regards,
Hans

PJ_in_FL
5StarLounger
Posts: 932
Joined: 21 Jan 2011, 16:51
Location: Florida

Re: Combine data of multiple worksheets on single sheet

Post by 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.
PJ in (usually sunny) FL

prince
2StarLounger
Posts: 153
Joined: 02 Mar 2015, 17:00

Re: Combine data of multiple worksheets on single sheet

Post by prince »

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

With warm regards
Prince