Google Apps Script开发需求：指定条件下同步表单数据至主表格

Alright, let's break down your problem and build a solution that fits exactly what you need. You've got two core tasks: copying the Data sheet to an independent table when H35 in the All sheet is set to "Yes", and appending daily entries from every team's Source sheet to a single master table. Here's how to make this happen using Excel VBA:

This will automatically copy your Data sheet to your target independent file whenever someone selects "Yes" in H35 of the All sheet:

• Open the VBA editor by pressing Alt + F11.
• In the Project Explorer (left pane), double-click the All sheet to open its code window.
• Paste this code into the window:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Only react if the changed cell is H35
    If Not Intersect(Target, Me.Range("H35")) Is Nothing Then
        ' Check if H35 is set to "Yes" (case-insensitive)
        If UCase(Me.Range("H35").Value) = "YES" Then
            ' Replace this path with your actual independent table file path
            Dim destFilePath As String
            destFilePath = "C:\Your\Folder\Path\IndependentTable.xlsx"
            
            Dim destWB As Workbook
            ' Check if the destination file is already open to avoid errors
            On Error Resume Next
            Set destWB = Workbooks.Open(destFilePath)
            On Error GoTo 0
            
            ' Copy the entire Data sheet to the destination workbook
            ThisWorkbook.Sheets("Data").Copy Before:=destWB.Sheets(1)
            
            ' Save and close the destination file
            destWB.Save
            destWB.Close SaveChanges:=False
        End If
    End If
End Sub

• Quick adjustments:
  • Swap C:\Your\Folder\Path\IndependentTable.xlsx with the full file path of your independent table.
  • If you only need a specific range (not the whole sheet), replace the copy line with something like:

    ThisWorkbook.Sheets("Data").Range("A1:Z50").Copy
    destWB.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteAll
    

    (Adjust the ranges to match your data.)

This macro will grab all daily entries from every "Source" sheet and add them to the next empty row of your master table:

• In the VBA editor, right-click your workbook in the Project Explorer > Insert > Module.
• Paste this code into the new module:

Sub AppendSourceDataToMaster()
    Dim masterSheet As Worksheet
    Dim sourceSheet As Worksheet
    Dim lastRowMaster As Long
    Dim lastRowSource As Long
    
    ' Replace "MasterTable" with your actual master sheet name
    Set masterSheet = ThisWorkbook.Sheets("MasterTable")
    
    ' Loop through every sheet in the workbook
    For Each sourceSheet In ThisWorkbook.Sheets
        ' Only process sheets named "Source" (case-insensitive, matches partial names like "Team1Source")
        If UCase(sourceSheet.Name) Like "*SOURCE*" Then
            ' Find the next empty row in the master table
            lastRowMaster = masterSheet.Cells(masterSheet.Rows.Count, "A").End(xlUp).Row + 1
            ' Find the last row with data in the current Source sheet
            lastRowSource = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
            
            ' Copy data (skips header row; remove the "2" if your Source sheets have no headers)
            sourceSheet.Range("A2:Z" & lastRowSource).Copy _
                Destination:=masterSheet.Range("A" & lastRowMaster)
        End If
    Next sourceSheet
    
    ' Optional: Uncomment this block to clear Source sheets after appending data
    ' For Each sourceSheet In ThisWorkbook.Sheets
    '     If UCase(sourceSheet.Name) Like "*SOURCE*" Then
    '         sourceSheet.Range("A2:Z" & sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row).ClearContents
    '     End If
    ' Next sourceSheet
    
    MsgBox "All Source data has been added to the master table!", vbInformation
End Sub

• Quick adjustments:
  • Replace "MasterTable" with the name of your main master sheet.
  • To make this easy to run daily, add a button to your workbook: Go to the Developer tab > Insert > Button (Form Control) > Select the AppendSourceDataToMaster macro when prompted.

• Always enable macros when opening the workbook (Excel will show a prompt).
• Test both macros with a copy of your data first to avoid accidental data loss.
• If your independent table is often open while working, the first code checks for that to prevent duplicate file openings.

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