请求合并两段VBA代码：工作表生成+自动添加求和公式

Got it, let's merge those two macros into one seamless process so your new sheets get generated, populated, and have the sum formulas added automatically. Here's how to do it:

We'll integrate the sum formula logic directly into your existing sheet-creation macro. I'll use a helper subroutine for the formula part to keep the code clean and reusable, but you can also embed it directly if you prefer.

Option Explicit

Sub SheetsFromTemplateWithSums()
    Dim templateSheet As Worksheet
    Dim newSheet As Worksheet
    Dim lastRowTemplate As Long
    Dim i As Long
    
    ' Set your template sheet (update the name to match your actual template)
    Set templateSheet = ThisWorkbook.Sheets("Template")
    
    ' Example: Loop through data to create sheets (adjust this part to match your original logic)
    ' Assuming your source data is in column A of the template or another sheet
    lastRowTemplate = templateSheet.Cells(templateSheet.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRowTemplate ' Skip header row if needed
        ' Create new sheet from template
        templateSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Set newSheet = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        
        ' Rename new sheet (adjust based on your data source)
        newSheet.Name = templateSheet.Cells(i, "A").Value
        
        ' Populate data into new sheet (replace this with your original data-filling logic)
        ' Example: Copy data from template row i to new sheet
        templateSheet.Rows(i).Copy newSheet.Rows(2)
        
        ' --- Add sum formulas to the new sheet ---
        Call AddSumFormulas(newSheet)
    Next i
    
    MsgBox "Sheets created and formulas added successfully!", vbInformation
End Sub

' Helper sub to add SUM formulas to columns L-AJ in the target sheet
Sub AddSumFormulas(targetSheet As Worksheet)
    Dim lastDataRow As Long
    Dim currentCol As Range
    
    ' Find the last row with data (adjust column "A" to match your data's key column)
    lastDataRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through columns L to AJ (columns 12 to 36)
    For Each currentCol In targetSheet.Range("L:AJ").Columns
        ' Add SUM formula in the first empty cell below the data
        targetSheet.Cells(lastDataRow + 1, currentCol.Column).Formula = _
            "=SUM(" & currentCol.Cells(1, 1).Resize(lastDataRow).Address(False, False) & ")"
    Next currentCol
End Sub

Key Integration Details: #

1. Helper Subroutine: The AddSumFormulas sub takes a worksheet as input, so we can pass each newly created sheet to it right after populating data.
2. Last Row Detection: Adjust the column reference ("A") in lastDataRow if your data starts in a different column (e.g., use "B" if your main data is in column B).
3. Formula Range: The loop covers columns L to AJ—if you need to adjust this range, modify targetSheet.Range("L:AJ") to your desired columns.
4. Match Your Original Logic: Replace the example data-filling code (copying rows from the template) with your original code that populates the new sheet.

Notes: #

• Always test this macro on a copy of your workbook first to avoid accidental data loss.
• Ensure your template sheet name matches the one specified in Set templateSheet = ....
• If your original sum formula used a different calculation (e.g., excluding header rows), adjust the Resize(lastDataRow) part to Resize(lastDataRow - 1) and start the range at row 2.

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