问Excel自动化错误:运行时错误'-2147417848 (80010108)‘

EN

templateSheet.Copy After:=indexSheet

    wb.Save
    Application.OnTime Now + TimeValue("00:00:01"), Application.Workbooks.Open(filePath)
    wb.Close (True)

Private Sub OkButton_Click()

'Dont update the screen while the macro runs
Application.ScreenUpdating = False

    'Sheet and workbook variables
    Dim wb As Workbook
    Dim indexSheet As Worksheet, templateSheet As Worksheet
    Dim templateCopy As Worksheet, newSheet As Worksheet

    'Table and new row variables
    Dim Tbl As ListObject
    Dim NewRow As ListRow

    'Variables to group shapes based on
    'need to hide or show them
    Dim hideShapes() As Variant, showShapes() As Variant
    Dim hideGroup As Object, showGroup As Object

    'Misc variables
    Dim i As Integer
    Dim exists As Boolean
    Dim filePath As String

    'Variables to assign ranges
    Dim scenarioRng As Range
    Dim traceabilityFocus As Range
    Dim testCaseRng As Range
    Dim statusRng As Range
    Dim newSheetTestCaseRng As Range
    Dim newSheetStatusRng As Range
    Dim newSheetFocus As Range
    Dim newSheetDateRng As Range

    'Create array of shapes based on visibility rules
    hideShapes = Array("TextBox 2", "Rectangle 1")
    showShapes = Array("TextBox 15", "TextBox 14", "TextBox 13", "TextBox 11", "StatsRec", "Button 10")

    'To reference Traceability Matrix sheet
    Set indexSheet = ThisWorkbook.Sheets("Traceability Matrix")
    'To reference Template sheet
    Set templateSheet = ThisWorkbook.Sheets("TestCase_Template")
    'To reference traceability matrix table
    Set Tbl = indexSheet.ListObjects("TMatrix")
    'Set hideShapes to a hide group
    Set hideGroup = indexSheet.Shapes.Range(hideShapes)
    'Set show shapes to a show group
    Set showGroup = indexSheet.Shapes.Range(showShapes)
    'To reference this workbook
    Set wb = ThisWorkbook
    'Get file path of this workbook and set it to string
    filePath = wb.FullName


    'If the userform fields are empty then show error message
    If ScenarioNameBox.Value = "" Or TestCaseNameBox.Text = "" Then
            MsgBox ("Please complete both fields.")
    'If the userform fields are completed and a worksheet with
    'the same name exists, set boolean to true
    Else
        For i = 1 To Worksheets.Count
        If ThisWorkbook.Worksheets(i).Name = TestCaseNameBox.Value Then
            exists = True
    End If
    'Iterate through all worksheets
    Next i

    'If test case name already exists, show error message
    If exists Then
        MsgBox ("This test case name is already in use. Please choose another name.")
    'If test case name is unique, update workbook
    Else
        'Copy template sheet to after traceability matrix sheet
        templateSheet.Copy After:=indexSheet 'LOCATION OF ERROR!!!
        'Ensure template sheet is hidden
        templateSheet.Visible = False

        'To reference copy of template
        Set templateCopy = ThisWorkbook.Sheets("TestCase_Template (2)")

        'Rename template sheet to the test case name
        templateCopy.Name = TestCaseNameBox.Value
        'To reference re-named template sheet
        Set newSheet = ThisWorkbook.Sheets(TestCaseNameBox.Value)
        'Show new sheet
        newSheet.Visible = True

        'Set focus to traceability matrix
        Set traceabilityFocus = indexSheet.Range("A1")

        'Add a new row
        Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)

        'Set ranges for cells in traceability table
        Set scenarioRng = indexSheet.Range("B" & NewRow.Range.Row)
        Set testCaseRng = scenarioRng.Offset(0, 1)
        Set statusRng = testCaseRng.Offset(0, 1)

        'Set scenario cell with name and format
        With scenarioRng
            .FormulaR1C1 = ScenarioNameBox.Value
            .HorizontalAlignment = xlGeneral
            .Font.Name = "Arial"
            .Font.Size = 12
        End With

        'Set test case cell with name, hyperlink to sheet, and format
        With testCaseRng
            .FormulaR1C1 = TestCaseNameBox.Value
            .Hyperlinks.Add Anchor:=testCaseRng, Address:="", SubAddress:=newSheet.Name & "!A1", TextToDisplay:=newSheet.Name
            .HorizontalAlignment = xlGeneral
            .Font.Name = "Arial"
            .Font.Size = 12
        End With

        'Set trial status as Incomplete and format
        With statusRng
            'Set new test case to "Incomplete"
            .Value = "Incomplete"
            .Font.Name = "Arial"
            .Font.Size = 12
            .Font.Color = vbBlack
        End With

        'Show or hide objects
        hideGroup.Visible = False
        showGroup.Visible = True

        'Set ranges for cells in test case table
        Set newSheetTestCaseRng = newSheet.Range("C2")
        Set newSheetStatusRng = newSheet.Range("C12")
        Set newSheetDateRng = newSheet.Range("C5")

        'Insert test case name into table
        newSheetTestCaseRng.Value = TestCaseNameBox.Value
        'Add todays date to Date Created
        newSheetDateRng.Value = Date
        'Set status to "Incomplete"
        newSheetStatusRng.Value = "Incomplete"
        'End with cursor at beginning of table
        newSheet.Activate
        Range("C3").Activate


        'wb.Save
        'Application.OnTime Now + TimeValue("00:00:01"), Application.Workbooks.Open(filePath)
        'wb.Close (True)


        'Close the userform
        Unload Me

        End If

    End If

    'Update screen
    Application.ScreenUpdating = True

End Sub

 With templateSheet
        .Visible = xlSheetVisible
        .Copy Before:=indexSheet 'ERRORS HERE!!
        .Visible = xlSheetVeryHidden