Combine sheet in master spreadsheet with auto update

Hello Alex,

If you are just trying to consolidate all the data in the Rep sheets into one master sheet, then this VBA macro code should do the task for you:-

Sub Consolidate()

    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

You may have to change the sheet name "Master" in the code to suit your workbook. The code refreshes the Master sheet each time the code is run, hence it will update the Master sheet if changes are made to the data sets of the Rep sheets.

I hope that this helps.

Cheerio,

vcoolio.

Hello Alex,

To do that, the same code can be placed into the ThisWorkbook module, with a minor adjustment as follows:-

Private Sub Workbook_Open()

   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

To implement this code:-

- Go to the VB Editor.

- Over to the left in the Project Explorer, double click on ThisWorkbook.

- In the big white code field to the right, paste the above code.

- Save and close the workbook.

Each time that the workbook is opened thereafter, the code will execute. However, please note that by doing it this way, each time that any Rep sheet is altered, you will need to save and close the workbook then open it again to see the updated data in the Master sheet.

I hope that this helps.

Cheerio,

vcoolio.

Is there any way to set up the macro to run each time the file is open? 

You're welcome Alex. I'm glad to have been able to assist and thanks for the feed back.

Cheerio,

vcoolio.