VBA多模板引用需求：按单元格值选择模板生成发票工作表

Got it, let's tweak your existing VBA code to handle the template switch based on the Hire column, plus fill in the missing functionality your original requirement didn't cover (like populating invoice data into the new sheets and preventing duplicates). Here's the complete, updated code with explanations:

Sub GenerateInvoiceSheets()
    Dim wsInvoices As Worksheet
    Dim wsTemplate As Worksheet
    Dim newWs As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim hireCol As Integer
    Dim invoiceNum As String
    Dim templateName As String
    
    ' Set reference to the Invoices worksheet
    Set wsInvoices = ThisWorkbook.Worksheets("Invoices")
    
    ' Find the column number for "Hire" header (avoids hardcoding)
    On Error Resume Next
    hireCol = wsInvoices.Rows(1).Find(What:="Hire", LookIn:=xlValues, LookAt:=xlWhole).Column
    On Error GoTo 0
    
    ' Exit if Hire column isn't found
    If hireCol = 0 Then
        MsgBox "Hire column not found in Invoices sheet!", vbExclamation
        Exit Sub
    End If
    
    ' Get last row with data in Invoices sheet
    lastRow = wsInvoices.Cells(wsInvoices.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each invoice row (skip header row 1)
    For r = 2 To lastRow
        ' Get invoice number (adjust column if your invoice number is in a different column)
        invoiceNum = wsInvoices.Cells(r, "A").Value
        
        ' Skip if invoice number is blank
        If invoiceNum = "" Then
            MsgBox "Blank invoice number found in row " & r & ", skipping.", vbWarning
            GoTo NextRow
        End If
        
        ' Determine which template to use based on Hire column (case-insensitive)
        Select Case LCase(wsInvoices.Cells(r, hireCol).Value)
            Case "y"
                templateName = "Hire Template"
            Case "n"
                templateName = "Template"
            Case Else
                MsgBox "Invalid value in Hire column (row " & r & "), use 'y' or 'n'. Skipping.", vbWarning
                GoTo NextRow
        End Select
        
        ' Check if template exists
        On Error Resume Next
        Set wsTemplate = ThisWorkbook.Worksheets(templateName)
        On Error GoTo 0
        
        If wsTemplate Is Nothing Then
            MsgBox "Template '" & templateName & "' not found! Skipping row " & r & ".", vbCritical
            GoTo NextRow
        End If
        
        ' Check if invoice sheet already exists
        On Error Resume Next
        Set newWs = ThisWorkbook.Worksheets(invoiceNum)
        On Error GoTo 0
        
        If Not newWs Is Nothing Then
            ' Prompt user to delete existing sheet or skip
            If MsgBox("Sheet for invoice " & invoiceNum & " already exists. Delete it?", vbYesNo) = vbYes Then
                Application.DisplayAlerts = False
                newWs.Delete
                Application.DisplayAlerts = True
            Else
                GoTo NextRow
            End If
        End If
        
        ' Copy template to new sheet and rename
        wsTemplate.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        Set newWs = ActiveSheet
        newWs.Name = invoiceNum
        
        ' --- Populate invoice data into new sheet (adjust these ranges to match your template) ---
        ' Example: Copy invoice number to cell B2 of new sheet
        newWs.Range("B2").Value = invoiceNum
        ' Copy customer name from column B of Invoices to cell B3 of new sheet
        newWs.Range("B3").Value = wsInvoices.Cells(r, "B").Value
        ' Copy amount from column C of Invoices to cell E5 of new sheet
        newWs.Range("E5").Value = wsInvoices.Cells(r, "C").Value
        ' Add more mappings as needed for your template
        
NextRow:
        ' Reset template reference for next loop
        Set wsTemplate = Nothing
        Set newWs = Nothing
    Next r
    
    MsgBox "Invoice sheet generation complete! Processed " & lastRow - 1 & " rows.", vbInformation
End Sub

Key Improvements & Missing Functionality Added #

• Dynamic Template Selection: Checks the Hire column (case-insensitive, so "Y"/"y" or "N"/"n" work) and uses the correct template automatically.
• Flexible Column Lookup: Uses Range.Find to locate the Hire column instead of hardcoding a column number—so if you rearrange your Invoices sheet later, the code still works.
• Duplicate Sheet Prevention: Checks if an invoice sheet already exists and lets you delete it or skip the row, avoiding runtime errors.
• Data Population: Fills in key invoice details into the new template sheets (you'll need to adjust the cell references to match your specific template's layout). This was the big missing piece from your original requirement, which only mentioned creating sheets without populating them.
• Error Handling: Catches common issues like missing templates, blank invoice numbers, or invalid Hire values, with clear user prompts instead of crashing.

Quick Tip: Hide your "Template" and "Hire Template" sheets (right-click the tab > Hide) to prevent accidental edits—this code will still copy them even when hidden.

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