Excel自动生成概览表：遍历工作表同步公司名称与D21单元格价格

I've got you covered on this automation task. We'll use VBA (Visual Basic for Applications) to make your overview sheet update automatically whenever you add a new company worksheet or modify the price in cell D21 of any company sheet. Here's the step-by-step implementation:

Step 1: Open the VBA Editor #

Press Alt + F11 to open the VBA editor in Excel. In the Project Explorer (left pane), double-click on ThisWorkbook to open its code window.

Step 2: Paste the VBA Code #

Copy and paste the following code into the ThisWorkbook code window:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    ' Update overview when a new sheet is added
    UpdateOverview
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' Only trigger if D21 is modified, and skip the overview sheet
    If Sh.Name <> "overview" And Target.Address = "$D$21" Then
        UpdateSpecificPrice Sh.Name, Target.Value
    End If
End Sub

Private Sub UpdateOverview()
    Dim wsOverview As Worksheet
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    Set wsOverview = ThisWorkbook.Worksheets("overview")
    
    ' Turn off events to prevent infinite loops
    Application.EnableEvents = False
    
    ' Clear existing data (keep header row 1)
    wsOverview.Range("A2:B" & wsOverview.Cells(wsOverview.Rows.Count, "A").End(xlUp).Row).ClearContents
    
    ' Loop through all worksheets
    i = 2 ' Start pasting data from row 2
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "overview" Then
            ' Add company name to column A
            wsOverview.Cells(i, "A").Value = ws.Name
            ' Add price from D21 to column B (handle empty cells)
            If Not IsEmpty(ws.Range("D21").Value) Then
                wsOverview.Cells(i, "B").Value = ws.Range("D21").Value
            End If
            i = i + 1
        End If
    Next ws
    
    ' Turn events back on
    Application.EnableEvents = True
End Sub

Private Sub UpdateSpecificPrice(companyName As String, newPrice As Variant)
    Dim wsOverview As Worksheet
    Dim matchRow As Variant
    
    Set wsOverview = ThisWorkbook.Worksheets("overview")
    
    ' Find the row with the matching company name
    matchRow = Application.Match(companyName, wsOverview.Range("A:A"), 0)
    
    ' Update price if match is found
    If Not IsError(matchRow) Then
        Application.EnableEvents = False
        wsOverview.Cells(matchRow, "B").Value = newPrice
        Application.EnableEvents = True
    End If
End Sub

How It Works #

• Workbook_NewSheet: This event triggers whenever you add a new worksheet. It calls the UpdateOverview subroutine to refresh the entire overview sheet—adding the new company name and pulling its current D21 price (if any).
• Workbook_SheetChange: This event fires when any cell is edited. It checks if the edited cell is D21 in a company sheet, then updates the corresponding price in the overview sheet immediately.
• UpdateOverview: Clears old data (preserving the header) and rebuilds the overview list by looping through all worksheets except "overview". It populates company names and their D21 prices.
• UpdateSpecificPrice: A helper sub that finds the matching company name in the overview sheet and updates its price without rebuilding the entire list (faster for single price changes).

Notes #

• Make sure your overview sheet has headers in row 1: company_name in cell A1, price in cell B1.
• If you rename an existing company worksheet, you'll need to run UpdateOverview manually (you can add a button to the overview sheet for this, or trigger it via another event if needed).
• Save your workbook as a Macro-Enabled Workbook (.xlsm) to retain the VBA code.

内容的提问来源于stack exchange，提问作者xralf