Excel多工作表Live行汇总及一键刷新按钮开发需求

Got it, let's tackle this Excel automation task—this is exactly the kind of repetitive work VBA was made for. Here's a step-by-step solution that'll let you pull all "Live" rows (where today's date falls between the start and end dates) from multiple worksheets, copy specific cells into a summary sheet, and add a one-click button to run it daily.

We'll use Excel VBA to:

• Loop through every worksheet in your workbook (skipping the summary sheet we'll create)
• Check each row to see if today's date is between the row's start date and end date
• Copy your specified cells from qualifying rows to the end of the summary sheet
• Add a form button to trigger this script with a single click

First, let's write the core automation code. Open the VBA editor (Alt + F11), insert a new module, and paste this code—make sure to adjust the parameters to match your actual spreadsheet structure:

Sub ExportLiveRows()
    Dim ws As Worksheet
    Dim summaryWs As Worksheet
    Dim lastRow As Long
    Dim currentRow As Long
    Dim todayDate As Date
    Dim startDateCol As Integer ' Replace with your start date column number (A=1, B=2, etc.)
    Dim endDateCol As Integer ' Replace with your end date column number
    Dim targetCols As Variant ' Replace with array of columns to copy (e.g., Array(1,3,5) = A,C,E)
    Dim summaryLastRow As Long
    Dim i As Integer
    
    ' Grab today's date
    todayDate = Date
    
    ' --- UPDATE THESE VALUES TO MATCH YOUR SHEET ---
    startDateCol = 2 ' Example: Start date is in column B
    endDateCol = 3 ' Example: End date is in column C
    targetCols = Array(1, 3, 5) ' Example: Copy columns A, C, E
    ' --- END OF CUSTOMIZATION ---
    
    ' Check if summary sheet exists; create it if not
    On Error Resume Next
    Set summaryWs = ThisWorkbook.Worksheets("Live汇总表")
    On Error GoTo 0
    
    If summaryWs Is Nothing Then
        Set summaryWs = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        summaryWs.Name = "Live汇总表"
        ' Optional: Add header labels here (uncomment and edit as needed)
        ' summaryWs.Range("A1").Value = "ID"
        ' summaryWs.Range("B1").Value = "Project Name"
        ' summaryWs.Range("C1").Value = "Status"
    End If
    
    ' Optional: Clear existing summary data (keep headers) - uncomment if needed
    ' summaryLastRow = summaryWs.Cells(Rows.Count, 1).End(xlUp).Row
    ' If summaryLastRow > 1 Then summaryWs.Rows("2:" & summaryLastRow).ClearContents
    
    ' Loop through all worksheets
    For Each ws In ThisWorkbook.Worksheets
        ' Skip the summary sheet itself
        If ws.Name <> summaryWs.Name Then
            lastRow = ws.Cells(Rows.Count, startDateCol).End(xlUp).Row ' Find last row with data
            
            ' Start at row 2 (assuming row 1 is headers)
            For currentRow = 2 To lastRow
                ' Check if today's date is between start and end (inclusive)
                If (ws.Cells(currentRow, startDateCol).Value <= todayDate) And (ws.Cells(currentRow, endDateCol).Value >= todayDate) Then
                    ' Find next empty row in summary sheet
                    summaryLastRow = summaryWs.Cells(Rows.Count, 1).End(xlUp).Row + 1
                    
                    ' Copy specified columns to summary sheet
                    For i = LBound(targetCols) To UBound(targetCols)
                        summaryWs.Cells(summaryLastRow, i + 1).Value = ws.Cells(currentRow, targetCols(i)).Value
                    Next i
                End If
            Next currentRow
        End If
    Next ws
    
    ' Let you know it's done
    MsgBox "Live rows have been successfully summarized!", vbInformation
End Sub

1. Customize the Code: Update the startDateCol, endDateCol, and targetCols variables to match your spreadsheet's column positions. If you need header labels for the summary sheet, uncomment and edit the header lines.
2. Test the Script: Press F5 in the VBA editor to run the code. Check the new "Live汇总表" sheet to make sure the correct rows are being pulled.
3. Add a One-Click Button:
   • If you don't see the Developer tab in Excel, enable it via File > Options > Customize Ribbon and check the "Developer" box.
   • Go to the Developer tab, click Insert, and select the Button (Form Control).
   • Draw the button on any worksheet, then select the ExportLiveRows macro from the popup window.
   • Right-click the button, select "Edit Text", and rename it to something like "Daily Live Summary".

• Date Formatting: Ensure your start date and end date columns are formatted as dates (not text) — otherwise the date comparison won't work.
• Header Rows: The code assumes your data starts at row 2 (row 1 is headers). If your sheet uses a different structure, adjust the currentRow = 2 To lastRow line.
• Protected Sheets: If any worksheets are protected, you'll need to unprotect them first (add ws.Unprotect Password:="yourpassword" before the loop) and re-protect after (add ws.Protect Password:="yourpassword" after the loop).
• Append vs. Overwrite: By default, the code appends new rows to the summary sheet. If you want to overwrite previous data each day, uncomment the "clear existing data" lines.

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