Update an Excel Summary Sheet When Worksheet Tabs Change with VBA
Question details
Create worksheets from names on a Summary sheet, link their totals, and manage renames and deletions.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A workbook uses a Master template and a Summary sheet to manage worksheets and display values from each one.
- Observed behavior
- The user needs Summary links to follow name changes and a button to remove created sheets safely.
Open the workbook in desktop Excel, save it as an Excel Macro-Enabled Workbook (.xlsm), and enable macros if prompted. Confirm that sheets named Summary and Master exist, that B13:D40 on Summary are available, and that C10 on each copied sheet contains the value to summarize.
Use a worksheet event and a removal macro
Create or rename tracked sheets by editing Summary cells B13:B40, and remove a tracked sheet with a button.
This example stores each managed sheet's last known name in column D and places its linked value in column C. Adjust the ranges and C10 reference if your workbook uses different cells.
Make name changes in column B, not directly on the worksheet tab. Manually added, renamed, or deleted tabs are not automatically synchronized; the stored name in column D must be reconciled first.
Press Alt+F11 in desktop Excel, find the workbook in Project Explorer, and double-click the Summary worksheet. Paste this code into its sheet module: Private Sub Worksheet_Change(ByVal Target As Range) Dim changed As Range, cell As Range Dim newName As String, oldName As String Dim ws As Worksheet, created As Boolean Dim r As Long Set changed = Intersect(Target, Me.Range("B13:B40")) If changed Is Nothing Then Exit Sub On Error GoTo Failed Application.EnableEvents = False For Each cell In changed.Cells Set ws = Nothing created = False r = cell.Row newName = Trim$(CStr(cell.Value)) oldName = CStr(Me.Cells(r, "D").Value) If Len(newName) > 0 Then If Len(oldName) = 0 Then ThisWorkbook.Worksheets("Master").Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) Set ws = ActiveSheet created = True ws.Name = newName created = False Else Set ws = ThisWorkbook.Worksheets(oldName) If StrComp(oldName, newName, vbBinaryCompare) <> 0 Then ws.Name = newName End If cell.Value = newName Me.Cells(r, "D").Value = ws.Name Me.Cells(r, "C").Formula = "=IFERROR(INDIRECT(""'""&SUBSTITUTE(B" & r & ",""'"",""''"")&""'!C10""),"""")" End If Next cell Done: Application.EnableEvents = True Exit Sub Failed: If created And Not ws Is Nothing Then Application.DisplayAlerts = False ws.Delete Application.DisplayAlerts = True End If MsgBox "Could not process row " & r & ". Check the sheet name and the tracked name in column D.", vbExclamation Resume Done End Sub
In the VBA editor, choose Insert > Module and paste this code: Public Sub RemoveTrackedSheet() Dim summary As Worksheet, ws As Worksheet Dim r As Long, sheetName As String Set summary = ThisWorkbook.Worksheets("Summary") If Not ActiveSheet Is summary Then MsgBox "Select a row on the Summary sheet first.", vbInformation Exit Sub End If r = ActiveCell.Row If r < 13 Or r > 40 Then MsgBox "Select a cell in rows 13 through 40.", vbInformation Exit Sub End If sheetName = CStr(summary.Cells(r, "D").Value) If Len(sheetName) = 0 Then MsgBox "No sheet is tracked in this row.", vbInformation Exit Sub End If If StrComp(sheetName, "Master", vbTextCompare) = 0 Or StrComp(sheetName, "Summary", vbTextCompare) = 0 Then MsgBox "The Master and Summary sheets cannot be removed by this macro.", vbExclamation Exit Sub End If On Error GoTo Failed Set ws = ThisWorkbook.Worksheets(sheetName) If MsgBox("Delete the tracked sheet '" & sheetName & "' and clear this row?", vbYesNo + vbQuestion) <> vbYes Then Exit Sub Application.EnableEvents = False Application.DisplayAlerts = False ws.Delete Application.DisplayAlerts = True summary.Range("B" & r & ":D" & r).ClearContents Application.EnableEvents = True Exit Sub Failed: Application.DisplayAlerts = True Application.EnableEvents = True MsgBox "The sheet could not be removed. Check the tracked name in column D and workbook protection.", vbExclamation End Sub
In Excel, open Developer > Insert and choose Button under Form Controls. Draw the button on Summary, select RemoveTrackedSheet in the Assign Macro dialog, and click OK.
Enter a valid, unused worksheet name in Summary!B13. Check that a copy of Master appears and C13 displays its C10 value. Change B13 to another valid name and verify that the tab and link update.
Select any cell in the corresponding Summary row, click the removal button, and confirm the sheet name in the prompt. Clearing a name in column B alone does not delete the sheet; column D retains the tracked name.

Work with summary spreadsheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and Microsoft Excel format compatibility for an easier move between applications. Excel VBA behavior and macro support can vary by WPS version and installation, so test this workbook before relying on its automation.

Frequently Asked Questions
Why doesn't renaming a worksheet tab update the INDIRECT link?
INDIRECT builds a reference from text in column B, so a manual tab rename does not change that text. Rename the sheet by editing its Summary cell, or reconcile columns B and D after a manual rename.
What happens if I clear a name in column B?
The example leaves the worksheet in place and keeps its tracked name in column D. To remove it, select that Summary row and use the assigned RemoveTrackedSheet button.
Why does the summary value appear blank?
The formula uses IFERROR to return a blank when the reference cannot be resolved. Check that the tab name matches column B and that the intended value is in C10; change the formula's C10 reference if needed.
Can I run this VBA solution in Excel for the web?
Excel for the web does not run VBA macros. Open the .xlsm workbook in desktop Excel and enable macros if prompted.
What if the macro says a sheet name cannot be processed?
Check the name in column B for invalid worksheet-name characters or a duplicate tab name. Also check that column D contains the existing tracked tab name and that workbook structure protection is not preventing sheet changes.




