Automatic Update & Consolidation of Multiple Worksheets to Summary/Master Worksheet

[ExcelNoob_111]

Hey All,

I'm looking for a way to automatically consolidate and update a summary/master worksheet from multiple worksheets within the same workbook.

Currently, I have 8 tabs/worksheets, using the same formatted tables, but I need all this data to consolidate and automatically update a summary/master worksheet. I have ZERO experience creating VB/macro coding, but I can actually manipulate what currently exists; so that may help.

I have searched the forum and came across similar requests, but not the same as I am requesting.

I wish for the summary/master sheet to auto sort by one of the columns (of my choosing), the ability to filter, and maintain the formatting of the existing tabs/worksheets.

If automatically consolidating and updating isn't possible, is there a way that a button can be created and placed on the summary/master tab that can be pressed and it does the job?

I have attached a sample of what I'm working on. The format, lists, and dropdowns, are all functioning. I just need a way to consolidate via a macro/VB button or automatically update the Summary/Master tab after each tab is updated or revised.

Any assistance will be helpful.

Thanks.
______________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Marc L]

Hi !

See Consolidate data from different excel files (VBA) !
​

 

[ExcelNoob_111]

Hello, and thank you for your response.

The tutorial is for consolidating multiple files. I am looking to consolidate multiple worksheets within the same workbook, save the header formatting from the worksheets, automatically update the summary/master sheet with every change in the reference sheets, and/or the ability to clear and replace the generated data after each run macro.

I've been playing around with the following code:

Option Explicit
Sub Summary()
    Dim ws As Worksheet
    Dim lr As Long, lrS As Long
    Dim s1 As Worksheet
    Set s1 = Sheets("Summary")
    s1.Range("A1:M" & Rows.Count).End(xlUp).Rows.ClearContents
    For Each ws In Worksheets
        If ws.Name <> "Summary" And ws.Name <> "Lists" Then
            lr = ws.Range("B" & Rows.Count).End(xlUp).Row
            lrS = s1.Range("A" & Rows.Count).End(xlUp).Row
            ws.Range("B3:M" & lr).Copy
            s1.Range("A" & lrS + 1).PasteSpecial xlPasteFormats, , , False
            s1.Range("A" & lrS + 1).PasteSpecial xlPasteAll, , , False
        End If
    Next ws
   
   
End Sub

Using the previously attached file as the guinea pig, the results are coming back with a generated consolidated list, as I wish for, but the header formatting is non-existent, and even though there is a line to clear contents after every run macro, the function doesn't work as so. After each run macro, the newly generated data is placed below the previous; so it becomes one long, continues, repeating table. I'm looking to prevent this from happening.

If I can get the features of the header format, automatic update of the summary/master sheet after each change in the reference sheets, and/or the clearing of data and regeneration of data, it would complete my request.

 

[Marc L]

Correct codeline to clear all data : S1.UsedRange.Clear …

Activate Macro recorder and do manually a Copy / Paste of headers,
you will get your easy free code !
​