VBA代码仅生效于当前工作表，需实现文件打开时全表执行未结束周数据隐藏

Hey, I get exactly what you're dealing with—having to manually run your VBA code on every worksheet is such a hassle. Let's fix this so your workbook automatically hides those future/unfinished weeks across all sheets the second you open it.

Here's the modified code you need #

First, you'll want to paste this into the ThisWorkbook module (not a regular worksheet module)—that's where workbook-level events live:

Private Sub Workbook_Open()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim currentDate As Date
    Dim i As Long
    
    ' Grab today's date for comparison
    currentDate = Date
    
    ' Loop through EVERY worksheet in your workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Optional: Skip sheets that don't match your structure (e.g., check header)
        If ws.Range("A1").Value <> "Week #" Then
            GoTo NextSheet
        End If
        
        ' Get the last row with data in column A (assuming headers are in row 1)
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        
        ' Unhide all rows first to avoid leftover hidden rows from previous runs
        ws.Rows.Hidden = False
        
        ' Loop through each week row (skip header row 1)
        For i = 2 To lastRow
            ' Hide the row if the week's end date (column B) is in the future
            If ws.Cells(i, "B").Value > currentDate Then
                ws.Rows(i).Hidden = True
            End If
        Next i
        
NextSheet:
    Next ws
End Sub

Key changes that make this work: #

• Workbook_Open() event: This triggers automatically as soon as you open the workbook—no more manual runs.
• Worksheet loop: The For Each ws In ThisWorkbook.Worksheets line makes sure we hit every sheet, not just the active one.
• Explicit worksheet references: Using ws.Cells and ws.Rows ensures we're always working on the right sheet, even if focus shifts.
• Unhide first: We reset all rows to visible before applying the hide logic—this prevents weird behavior if you open/close the workbook multiple times.
• Optional structure check: The If ws.Range("A1").Value <> "Week #" skip lets you ignore sheets that don't follow your A/B column layout (remove this if all sheets are identical).

Quick notes to avoid issues: #

• Save your workbook as an Excel Macro-Enabled Workbook (.xlsm)—regular .xlsx files strip macros.
• Adjust the date condition if you need to hide other rows (e.g., past weeks older than a certain period). Just modify the If ws.Cells(i, "B").Value > currentDate line to match your needs.
• If your header is in a different row (not row 1), update the i = 2 To lastRow part to start at the correct row.

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