Whenever we are having an Excel file, wherever after a fixed interval we are adding
one sheet, then we sometimes require a summary sheet to club all the sheets detail,
but there is no direct feature in Excel where a summary automatically gets updated
on addition of a new sheet is added. For that, the Excel macro sample code below will help you in preparing a summary sheet for all sheets.
Sub GetData(Optional A As Integer)
Dim i, x As Integer
x = 1
For i = 3 To ActiveWorkbook.Sheets.Count
With ActiveWorkbook
If .Sheets(i).Visible <> xlSheetHidden Or .Sheets(i).Visible = xlSheetVeryHidden Then
.Sheets(1).Range("A1").Offset(x, 0).Value = .Sheets(i).Range("C9").Value
.Sheets(1).Range("A1").Offset(x, 1).Value = .Sheets(i).Range("E9").Value
.Sheets(1).Range("A1").Offset(x, 2).Value = .Sheets(i).Range("E16").Value
x = x + 1
End If
End With
Next i
End Sub
In the above code, i refers to the sheet no. from where to start & i =3 means
that data updation shall start from sheet 3, x refers to column number and range C9,
E9, E16 refers to the cells in sheets 3 onwards which are required in summary.
You can add more by copying the code: .Sheets(1).Range("A1").Offset(x,
2).Value = .Sheets(i).Range("CELL NUMBER").Value
Good examples for use of this code are summary sheets when monthly data is generated
and each sheet is for separate month or a user form is created, where every form
has different entry.