excel - 我怎样才能停止这个 vba 循环？

Question

我正在研究一个宏，以在工作簿中的所有工作表中循环我的步骤，如下所示。但是，它出现了一个错误：

运行时错误“1004”：“工作表类的选择方法失败”

Sub WorksheetLoopFormat()

     Dim WS_Count As Integer
     Dim i As Integer

     ' Set WS_Count equal to the number of worksheets in the active
     ' workbook.
     WS_Count = ActiveWorkbook.Worksheets.Count

     ' Begin the loop.
     For i = 2 To WS_Count

        Sheets(i).Select
        Range("C:C,G:G,I:I,AN:AN").Select
        Range("AN1").Activate
        Selection.Copy
        Sheets.Add After:=ActiveSheet
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("C30").Select
        Sheets(i).Select
        Application.CutCopyMode = False
        ActiveWindow.SelectedSheets.Delete

     Next i

  End Sub

希望有人能帮帮我！！非常感谢！！

标签： excelvba

Answer 1

在我看来，以下所有内容都可以帮助您构建结构良好的代码：

Option Explicit

Sub LoopSheets()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets

        With ws
            Debug.Print .Name
        End With

    Next

  End Sub

Sub AddSheet()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

    ws.Name = "Test"

End Sub

Sub Copy_Paste()

    Sheet1.Range("A1:D1").Copy Sheet2.Range("A1:D1")

End Sub

Sub DeleteSheet()

    ThisWorkbook.Worksheets("Test").Delete

End Sub