我试图使用UserForm通过VBA将数据输入到Excel中。我需要将表单中的数据输入到主摘要样式表以及新创建的工作表中。我使用的代码如下。

Private Sub btnSubmit_Click()
    CME_MACROS.CopyHiddenSheetWithFormattingAndMacros
    CME_MACROS.AddProviderToMain
    
    'Clear input values
    tbxFName.Value = ""
    tbxLName.Value = ""
    tbxAnniversary_Month.Value = ""
    tbxRenewal_Amount.Value = ""
    
    'Notify user that data has been added
    MsgBox "Data has been added to the table."
End Sub




Public Sub CopyHiddenSheetWithFormattingAndMacros()
    Dim templateSheet As Worksheet
    Set templateSheet = ThisWorkbook.Sheets("Template")
    'Define variables to hold input values
    Dim First_Name As String
    Dim Starting_CME As Integer
    Dim Last_Name As String
    Dim Anniversary_Month As String
    Dim Renewal_Amount As String
    
    'Get input values from user
    First_Name = ufAddProvider.tbxFName.Value
    Last_Name = ufAddProvider.tbxLName.Value
    Anniversary_Month = ufAddProvider.tbxAnniversary_Month.Value
    Renewal_Amount = ufAddProvider.tbxRenewal_Amount.Value
    
    If templateSheet.Visible = False Then
        templateSheet.Visible = True
    End If
    
    Dim newSheet As Worksheet
    Set newSheet = ThisWorkbook.Sheets.Add(Before:=templateSheet)
    templateSheet.Cells.Copy newSheet.Cells
    newSheet.Name = First_Name & " " & Last_Name
    newSheet.Range("B1").Value = First_Name & " " & Last_Name
    newSheet.Range("B3").Value = Renewal_Amount
    newSheet.Range("B4").Value = Anniversary_Month
    
    templateSheet.Visible = False
    newSheet.Visible = True
End Sub

Public Sub AddProviderToMain()
    'Define variables to hold input values
    Dim First_Name As String
    Dim Starting_CME As Integer
    Dim Last_Name As String
    Dim Anniversary_Month As String
    Dim Renewal_Amount As String
    
    'Get input values from user
    First_Name = ufAddProvider.tbxFName.Value
    Last_Name = ufAddProvider.tbxLName.Value
    Anniversary_Month = ufAddProvider.tbxAnniversary_Month.Value
    Renewal_Amount = ufAddProvider.tbxRenewal_Amount.Value
    
    'Insert new row into table
    With ThisWorkbook.Worksheets("Main").ListObjects("Providers").ListRows.Add
        .Range(1, 1).Value = First_Name
        .Range(1, 2).Value = Last_Name
        .Range(1, 3).Value = Anniversary_Month
        .Range(1, 4).Value = Renewal_Amount
        .Range(1, 5).Value = Renewal_Amount
    .Range(1, 6).Value = "Test" 

    End With

End Sub

当我尝试上面的代码时，它创建了工作表并在主汇总表中添加了一行。