Updating master sheet with data from multiple sheets

Bpuad

New Member
Joined
Feb 17, 2012
Messages
36
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I've a problem that I can't find the solution to.

I have multiple data sheets with tables and I want to create a master sheet table that automaticly will update when now rows/data is added in to the sheets in any of the data sheets.

Master sheet will look like this (the first 3 letters is the data sheet name), this is just the first column there a a lot more columns to be added

AAB08
AAB09
AAB10
AAB11
AAB12
AAC08
AAC09
AAC10
AAC11
AAC12
AAD08
AAD09
AAD10
AAD11
AAD12

So when let's say in sheet AAB I add another row AAD13 I want the master sheet to update automaticly so it looks like this

AAB08
AAB09
AAB10
AAB11
AAB12
AAB13
AAC08
AAC09
AAC10
AAC11
AAC12
AAD08
AAD09
AAD10
AAD11
AAD12

Is this possible?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Paste this into the Master sheet's Code. Make sure to test it in a backup copy. Change references as necessary. Tested on a dummy file with 105 sheets without data with 0.007s activation time. With all other sheets calculating 1000 cells, it activated in 0.08s. I believe that it can handle calculations well.

Code:
Private Sub Worksheet_Activate()

    StartTime = Timer


    Dim Master, Slave As Worksheet
    Dim SheetNameArray() As Variant
    Dim SheetCount As Long
    
    Set Master = ThisWorkbook.Sheets("Master")
    SheetCount = 0
    
    For Each Slave In ThisWorkbook.Worksheets
        If Slave.Visible = xlSheetVisible Then
            If Slave.Name <> "Master" Then
                SheetCount = SheetCount + 1
                ReDim Preserve SheetNameArray(1 To SheetCount)
                SheetNameArray(SheetCount) = Slave.Name
            End If
        End If
    Next Slave
    
    Master.Range("A1").Resize(SheetCount, 1).Value = _
        Application.Transpose(SheetNameArray)
        
    Debug.Print Timer - StartTime
        
End Sub

Let me know if this works.

Thanks,

J.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top