Use VBA to consolidate data from sheets 2 onwards into one new sheet called 'Master'

**webcam723** · 07-12-2023, 09:28 PM

Hi experts!

I am looking to use VBA to consolidate data from sheets 2 onwards into one new sheet called 'Master' to be placed in the second sheet position.

The data worksheets all have the same table format with data from Column A to T, I wish to only keep the header row (row 1) ONCE and start the data from row 2 onwards.

**wk9128** · 07-12-2023, 09:45 PM

Hi webcam723
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to.
Members tailor answers based on your Excel version. Your profile does not indicate your version 16.72.

Pls try this code

Sub ConsolidateData()
    Dim ws As Worksheet
    Dim masterWs As Worksheet
    Dim lastRow As Long
    Dim rngSource As Range
    Dim rngDestination As Range
    
    ' Create a new worksheet called "Master" and place it in the second sheet position
    Set masterWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(1))
    masterWs.Name = "Master"
    
    ' Loop through each worksheet starting from the second sheet
    For Each ws In ThisWorkbook.Sheets
        If ws.Index <> masterWs.Index Then
            ' Find the last row in the source worksheet
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        
            ' Set the source range (excluding the header row)
            Set rngSource = ws.Range("A2:T" & lastRow)
        
            ' Find the last row in the destination sheet
            lastRow = masterWs.Cells(masterWs.Rows.Count, "A").End(xlUp).Row
        
            ' Set the destination range in the master worksheet
            Set rngDestination = masterWs.Range("A" & lastRow + 1)
        
            ' Copy the source range to the destination range
            rngSource.Copy rngDestination
        End If
    Next ws
End Sub

**webcam723** · 07-12-2023, 10:21 PM

For Each ws In ThisWorkbook.Sheets(2).Worksheets

In the above line of code, the error 'Object doesnt support this property or method' occurs.

**vcoolio** · 07-12-2023, 10:41 PM

Hello Webcam,

Create the Master sheet manually and place your required headings in row1, then try this simplified code:

Sub Test()

    Dim ws As Worksheet, wsM As Worksheet
    Set wsM = Sheets("Master")
    
    Application.ScreenUpdating = False
    
    wsM.UsedRange.Offset(1).Clear
    
        For Each ws In Worksheets
              If ws.Name <> "Master" Then
                   ws.UsedRange.Offset(1).Copy wsM.Range("A" & Rows.Count).End(3)(2)
              End If
        Next ws
    
    Application.ScreenUpdating = True

End Sub

There is no need to have a new sheet created each time the code is run. The above code clears the Master sheet each time that the code is run.

I hope that this helps.

Cheerio,
vcoolio.

**webcam723** · 07-13-2023, 02:20 AM

Hi thanks alot!

Ok so now my 'Master' sheet is the first sheet. I got another sheet in second position that I dont want to use the data from.

How can i edit the code such that it uses the 3rd sheet onwards?

**vcoolio** · 07-13-2023, 02:36 AM

Hello Webcam,

Just exclude the other sheet as well:

Sub Test()

    Dim ws As Worksheet, wsM As Worksheet
    Set wsM = Sheets("Master")
    
    Application.ScreenUpdating = False
    
    wsM.UsedRange.Offset(1).Clear
    
        For Each ws In Worksheets
              If ws.Name <> "Master" And ws.Name <> "Sheet2" Then  '---->Change "Sheet2" to suit.
                   ws.UsedRange.Offset(1).Copy wsM.Range("A" & Rows.Count).End(3)(2)
              End If
        Next ws
    
    Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

Use VBA to consolidate data from sheets 2 onwards into one new sheet called 'Master'

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Use VBA to consolidate data from sheets 2 onwards into one new sheet called 'Master'

Re: Use VBA to consolidate data from sheets 2 onwards into one new sheet called 'Master'

Re: Use VBA to consolidate data from sheets 2 onwards into one new sheet called 'Master'

Re: Use VBA to consolidate data from sheets 2 onwards into one new sheet called 'Master'

Re: Use VBA to consolidate data from sheets 2 onwards into one new sheet called 'Master'

Re: Use VBA to consolidate data from sheets 2 onwards into one new sheet called 'Master'

Thread Information

Users Browsing this Thread

Similar Threads

How to consolidate data from multiple sheets onto single master sheet

Consolidate several sheets data in a master sheets

[SOLVED] Macro to Create a Master Sheet and consolidate data as per Headings on all Sheets

[SOLVED] How to consolidate all data from multiple sheets into a master table.

looking to take multiple weekly sales sheets and consolidate them into one master sheet

Consolidate all the sheets into one master sheet

Identify data in monthly sheets in WB and consolidate into master sheet

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions