Summary Sheet Automation whenever a new Excel Sheet is created

The Excel Macro code will help you to Auto update and create a Summary sheet whenever a new sheet is inserted in that Excel file.

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.

By Tarun Vir   Popularity  (1381 Views)