Excel需求：基于主表数据创建命名工作表并自动填充电话日志模板

Got it, let's tackle this problem step by step. You want to automate creating new worksheets from your master contact data and populate a phone log template—here's a practical, Excel-native solution using VBA, which is the most straightforward way to handle this kind of repetitive task.

1. Pre-Work Setup #

• Rename your master data sheet to MasterData (you can adjust this in the code later if you keep a different name) — make sure its headers match exactly what you listed: Company, Type, First, Last, TITLE, EMAIL, PHONE, US_MAIL_AD, US_MAIL_ADline2, CITY, STATE, ZIP
• Prepare your phone log template sheet, name it PhoneLogTemplate (again, tweakable in code). Note down the cell positions where you need to populate each piece of data (e.g., template cell A2 = Company name, B3 = Full contact name, etc.)
• Optional: Hide the template sheet to avoid accidental edits — right-click the template tab > Hide

2. Insert & Customize the VBA Code #

• Press Alt + F11 to open the VBA Editor
• Right-click your workbook name in the left pane > Insert > Module
• Paste the code below, then adjust the cell mappings to match your template's layout:

Sub GeneratePhoneLogSheets()
    Dim wsMaster As Worksheet
    Dim wsTemplate As Worksheet
    Dim newSheet As Worksheet
    Dim lastRow As Long
    Dim rowNum As Long
    Dim sheetName As String
    
    ' Link to your master and template sheets (update names if needed)
    Set wsMaster = ThisWorkbook.Worksheets("MasterData")
    Set wsTemplate = ThisWorkbook.Worksheets("PhoneLogTemplate")
    
    ' Find the last row with data in the master sheet
    lastRow = wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each data row (skip header row 1)
    For rowNum = 2 To lastRow
        ' Create a unique sheet name (company + contact to avoid duplicates)
        sheetName = wsMaster.Cells(rowNum, "A").Value & "_" & wsMaster.Cells(rowNum, "C").Value & "_" & wsMaster.Cells(rowNum, "D").Value
        
        ' Copy template and rename the new sheet
        wsTemplate.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        Set newSheet = ActiveSheet
        newSheet.Name = sheetName
        
        ' Populate template cells (UPDATE THESE CELL REFERENCES TO MATCH YOUR TEMPLATE!)
        newSheet.Range("A2").Value = wsMaster.Cells(rowNum, "A").Value ' Company
        newSheet.Range("B2").Value = wsMaster.Cells(rowNum, "B").Value ' Type
        newSheet.Range("A3").Value = wsMaster.Cells(rowNum, "C").Value & " " & wsMaster.Cells(rowNum, "D").Value ' Full Name (First + Last)
        newSheet.Range("B3").Value = wsMaster.Cells(rowNum, "E").Value ' Job Title
        newSheet.Range("A4").Value = wsMaster.Cells(rowNum, "F").Value ' Email
        newSheet.Range("B4").Value = wsMaster.Cells(rowNum, "G").Value ' Phone
        
        ' Build full address from master fields
        Dim fullAddress As String
        fullAddress = wsMaster.Cells(rowNum, "H").Value
        If wsMaster.Cells(rowNum, "I").Value <> "" Then fullAddress = fullAddress & ", " & wsMaster.Cells(rowNum, "I").Value
        fullAddress = fullAddress & ", " & wsMaster.Cells(rowNum, "J").Value & ", " & wsMaster.Cells(rowNum, "K").Value & " " & wsMaster.Cells(rowNum, "L").Value
        newSheet.Range("A5").Value = fullAddress ' Combined Address
    Next rowNum
    
    MsgBox "All phone log sheets have been created!", vbInformation
End Sub

3. Key Customization Tips #

• Sheet Naming: The code uses Company_First_Last for unique sheet names. If you have duplicate contacts, add a row number (e.g., sheetName = sheetName & "_" & rowNum) to avoid errors.
• Data Mappings: The lines like newSheet.Range("A2").Value = ... are where you connect master data to template cells. Replace A2 with the actual cell in your template that needs that data point.
• Address Handling: The code merges all address fields into one line. If your template has separate fields for street, city, etc., just map each master address column to its own template cell instead of combining them.

4. Run the Code #

• Switch back to Excel, press Alt + F8, select GeneratePhoneLogSheets, then click Run.
• Once it finishes, you'll see a pop-up confirmation, and each master data entry will have its own filled-out phone log sheet.

Quick Notes #

• If you have hundreds of rows, the code may take a minute to run—just let it finish.
• Unprotect your template sheet first if it's password-protected, otherwise the code can't copy or edit it.
• Always back up your workbook before running VBA code, just to be safe.

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