excel - 通过不同和特定的工作表循环代码

Question

我对 VBA 还很陌生，我正在努力变得更好。我有一个工作簿，我正在尝试循环代码。但仅限于特定的工作表 - 而不是整本书。基本上，我编写了一个代码，将数据透视表重新格式化为表格，并格式化标题等。这在一张纸上完美运行。但是我还有 10 张需要完成的工作表 - 对于我的同事(他们不是 excel 向导，如果可以通过按一下按钮来完成会更好 - 可以这么说)。
我已经用谷歌搜索了几个小时并尝试了许多不同的东西，有时我没有收到错误，但在作为宏运行时代码不会应用于其他工作表。
(B11 是所有工作表的静态起点)
这是代码:

     Sub Ultimo_Pivot_Table()

'Start Loop? 
        
        'Select and copy pivot
            Columns("B:O").Select
            Selection.Copy
            Columns("P:P").Select
        'Paste pivot in new area
            Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
                , SkipBlanks:=False, Transpose:=False
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        'Delete old pivot
            Columns("B:O").Select
            Range("O1").Activate
            Application.CutCopyMode = False
            Selection.Delete Shift:=xlToLeft
        'Select & Format as table
        With Range("B11")
            .Parent.ListObjects.Add(xlSrcRange, Range(.End(xlDown), .End(xlToRight)), , xlYes).Name = "Table1"
        End With
        'Format Headlines
        With Range("B11")
            Range(Selection, Selection.End(xlToRight)).Select
        End With
            With Selection
                .HorizontalAlignment = xlCenter
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
            With Selection
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
            With Selection.Font
                .ThemeColor = xlThemeColorLight1
                .TintAndShade = -0.499984740745262
            End With
            Range("B2").Select
            With Selection
                .HorizontalAlignment = xlLeft
                .VerticalAlignment = xlCenter
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
'End Loop? 

        End Sub

我尝试了很多不同的东西，所以这是原始代码，没有任何循环尝试。有什么建议么？谢谢!

Answer 1

未经测试，但你应该明白:

Sub Tester()

    Dim ws As Worksheet
    'loop over the sheets in the workbook containing this code
    For Each ws In ThisWorkbook.Worksheets
        'call the sub and pass the sheet if there's a pivottable
        If ws.PivotTables.Count = 1 Then Ultimo_Pivot_Table ws
    Next ws

End Sub

Sub Ultimo_Pivot_Table(ws As Worksheet)

    Dim lo As ListObject
        
    ws.Columns("B:O").Copy
    With ws.Range("P1")
        .PasteSpecial Paste:=xlPasteAllUsingSourceTheme
        .PasteSpecial Paste:=xlPasteValues
    End With
    ws.Columns("B:O").Delete Shift:=xlToLeft
    
    Set lo = ws.ListObjects.Add(xlSrcRange, ws.Range("B11").CurrentRegion, , xlYes)
    lo.Name = "Table1"
    
    With lo.HeaderRowRange
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Font.ThemeColor = xlThemeColorLight1
        .Font.TintAndShade = -0.499984740745262
    End With
    
    With ws.Range("B2")
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = False
    End With

End Sub