如何编写VBA代码，运行数据透视表宏时无需手动修改工作表编号与名称？

Hey there! I’ve refactored your VBA code to eliminate hardcoded sheet references and manual cell selections, making it more robust and flexible. Here’s what changed and the final working code:

Key Improvements: #

• Dynamic Data Source: Instead of hardcoding Orders!R1C1:R51291C24, we use the used range of your source sheet (you can adjust this to a specific table/range if needed)
• No Hardcoded Pivot Sheet: We create a new sheet for the pivot table instead of relying on an existing sheet with a fixed name
• Avoid Select/Activate: Directly reference objects (worksheets, pivot tables) instead of selecting cells, which makes the code faster and less error-prone
• Reusable Variables: Store the pivot table and source sheet in variables to simplify repeated references

Optimized Code: #

Sub CreateDynamicPivotTable()
    Dim dataSheet As Worksheet
    Dim pivotSheet As Worksheet
    Dim pivotCache As PivotCache
    Dim pivotTable As PivotTable
    Dim pivotTableName As String
    Dim newPivotSheetName As String
    
    ' Set source sheet to the currently active sheet (or change to a specific sheet like ThisWorkbook.Sheets("YourDataSheet"))
    Set dataSheet = ActiveSheet
    pivotTableName = "PivotTable1"
    newPivotSheetName = dataSheet.Name & "Pivot"
    
    ' Check if pivot sheet already exists; delete it if needed (optional, adjust as per your needs)
    On Error Resume Next
    Set pivotSheet = ThisWorkbook.Sheets(newPivotSheetName)
    If Err.Number = 0 Then
        MsgBox "Pivot sheet already exists. Deleting existing sheet to create new one.", vbInformation
        Application.DisplayAlerts = False
        pivotSheet.Delete
        Application.DisplayAlerts = True
    End If
    On Error GoTo 0
    
    ' Create new pivot sheet
    Set pivotSheet = ThisWorkbook.Sheets.Add(After:=dataSheet)
    pivotSheet.Name = newPivotSheetName
    
    ' Create pivot cache using dynamic source range (used range of data sheet)
    Set pivotCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=dataSheet.UsedRange, _
        Version:=xlPivotTableVersion15) ' Use appropriate version for your Excel
    
    ' Create pivot table on the new sheet
    Set pivotTable = pivotCache.CreatePivotTable( _
        TableDestination:=pivotSheet.Range("A1"), _
        TableName:=pivotTableName)
    
    ' Configure pivot fields
    With pivotTable.PivotFields("Country")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    ' Add data fields
    pivotTable.AddDataField pivotTable.PivotFields("Sales"), "Sum of Sales", xlSum
    pivotTable.AddDataField pivotTable.PivotFields("Profit"), "Sum of Profit", xlSum
    
    ' Add calculated field: Cost Incurred
    pivotTable.CalculatedFields.Add "Cost incurred", "=Sales-Profit", True
    pivotTable.PivotFields("Cost incurred").Orientation = xlDataField
    
    ' Add Shipping Cost data field
    pivotTable.AddDataField pivotTable.PivotFields("Shipping Cost"), "Sum of Shipping Cost", xlSum
    
    ' Add calculated field: % of Shipping Cost
    pivotTable.CalculatedFields.Add "% of shipping cost", "='Shipping Cost'/'Cost incurred'", True
    pivotTable.PivotFields("% of shipping cost").Orientation = xlDataField
    
    ' Format number styles
    With pivotTable.DataBodyRange
        .Columns(1).NumberFormat = "$#,##0.00" ' Sum of Sales
        .Columns(2).NumberFormat = "$#,##0.00" ' Sum of Profit
        .Columns(3).NumberFormat = "$#,##0.00" ' Cost Incurred
        .Columns(4).NumberFormat = "$#,##0.00" ' Sum of Shipping Cost
        .Columns(5).Style = "Percent" ' % of shipping cost
    End With
    
    ' Set pivot table layout
    With pivotTable
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    
    ' Disable GetPivotData (optional, keeps your sheet clean)
    Application.GenerateGetPivotData = False
    
    ' Optional: Move to pivot sheet
    pivotSheet.Activate
End Sub

Additional Notes: #

• If your data is in an Excel Table (ListObject), replace dataSheet.UsedRange with dataSheet.ListObjects(1).Range for even more reliability (it automatically expands with new data)
• The code checks for an existing pivot sheet and deletes it—you can remove this section if you want to keep old pivot tables
• Adjust the number formatting columns if the order of your data fields changes

内容的提问来源于stack exchange，提问作者harold quek