Summarize the data

[Kmahraz]

Hello,
Looking for some help, I have several sheets in my file and would like to create a summary in one of the sheets using VBA, the challenge I have is that we keep adding sheets to the workbook each time we receive a new project and would like help with a way using a command button or when the file is open to take the data from each of the sheets within the file and create a summary in the summary sheet.
Any help will be much appreciated!

K

 

[Marc L]

Hi !

Paste next code to ThisWorkbook module :​

Private Sub Summarize()
    Dim R&, V
With Worksheets(1)
    .UsedRange.Offset(1).Clear
    Application.ScreenUpdating = False
For R = 2 To Worksheets.Count
    V = Worksheets(R).UsedRange.Offset(1).Columns(2).Value
    .Cells(R, 1).Resize(, UBound(V)).Value = Application.Transpose(V)
Next
End With
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Open()
    If ActiveSheet.Index = 1 Then Summarize
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Index = 1 Then Summarize
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Kmahraz]

Thank you so much @Marc L