Let's break down why your code works smoothly on Mac Excel 365 but fails on PC Excel Professional Plus—there are several syntax and object-handling issues in your code that are likely causing the discrepancy, especially since non-auto-updating PC Excel versions tend to be stricter about these details.

1. Invalid PivotCache & PivotTable Chaining #

Your current code tries to chain CreatePivotTable directly to PivotCaches.Create, which is a critical mistake. The CreatePivotTable method returns a PivotTable object, not a PivotCache. This means your PCache variable is actually storing a PivotTable, and when you later call PCache.CreatePivotTable, it throws an error that Mac Excel might suppress but PC Excel refuses to ignore.

Fix this by separating PivotCache creation from PivotTable setup:

' Define Pivot Cache (standalone step)
Set PCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=PRange, _
    Version:=xlPivotTableVersionCurrent) ' Use version agnostic to target running Excel

' Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable( _
    TableDestination:=PSheet.Range("B3"), _
    TableName:="Total Backlog")

2. Outdated Pivot Table Version Target #

Using xlPivotTableVersion14 locks your code to Excel 2010's pivot table structure. If your PC Excel Professional Plus is a newer (but non-updated) version, this can cause compatibility breaks. Swap it for xlPivotTableVersionCurrent to align with the running Excel instance.

3. Case Sensitivity in Worksheet Names #

Mac Excel ignores case when referencing worksheet names, but PC Excel is case-sensitive. Double-check that your data sheet on PC is named exactly "US Master Macro" (matching capitalization). If it's named "US MASTER MACRO" or "us master macro", PC Excel will throw a "subscript out of range" error.

4. Overly Broad Error Suppression #

On Error Resume Next hides critical errors (like missing worksheets or invalid object references) that would otherwise tell you what's broken. Replace it with targeted error checks instead. For example, verify the "US MASTER" sheet exists before deleting:

' Check for existing "US MASTER" sheet before deletion
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("US MASTER")
On Error GoTo 0
If Not ws Is Nothing Then
    Application.DisplayAlerts = False
    ws.Delete
    Application.DisplayAlerts = True
End If

5. Hardcoded Sort Range #

Your code sorts Range("C4:C100"), which relies on assumptions about the pivot table's size. Instead, sort the pivot table's actual data range to ensure it works no matter how many rows the pivot generates:

' Sort pivot table directly (no hardcoded ranges)
With PSheet.PivotTables("Total Backlog").PivotFields("Classified Cases in Ranges")
    .AutoSort xlDescending, "Count of PR ID"
End With

Revised Full Code #

Here's the fixed version incorporating all these changes:

Sub Button1_Click()
    ' Declare Variables
    Dim PSheet As Worksheet
    Dim DSheet As Worksheet
    Dim PCache As PivotCache
    Dim PTable As PivotTable
    Dim PRange As Range
    Dim LastRow As Long
    Dim LastCol As Long
    Dim ws As Worksheet
    
    ' Check for existing "US MASTER" sheet before deletion
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("US MASTER")
    On Error GoTo 0
    If Not ws Is Nothing Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    End If
    
    ' Insert new blank worksheet
    Sheets.Add Before:=ActiveSheet
    ActiveSheet.Name = "US MASTER"
    Set PSheet = Worksheets("US MASTER")
    
    ' Verify data sheet exists (avoid case sensitivity issues)
    On Error Resume Next
    Set DSheet = ThisWorkbook.Worksheets("US Master Macro")
    On Error GoTo 0
    If DSheet Is Nothing Then
        MsgBox "Data sheet 'US Master Macro' not found!", vbExclamation
        Exit Sub
    End If
    
    ' Define data range
    LastRow = DSheet.Cells(DSheet.Rows.Count, 1).End(xlUp).Row
    LastCol = DSheet.Cells(1, DSheet.Columns.Count).End(xlToLeft).Column
    Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
    
    ' Create pivot cache
    Set PCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=PRange, _
        Version:=xlPivotTableVersionCurrent)
    
    ' Create pivot table
    Set PTable = PCache.CreatePivotTable( _
        TableDestination:=PSheet.Range("B3"), _
        TableName:="Total Backlog")
    
    ' Add PR ID to values field
    With PTable.PivotFields("PR ID")
        .Orientation = xlDataField
        .Function = xlCount
        .Position = 1
        .Name = "Count of PR ID" ' Explicit name to avoid auto-generated variations
    End With
    
    ' Add row field
    PTable.AddFields RowFields:=Array("Classified Cases in Ranges")
    
    ' Sort pivot table
    With PTable.PivotFields("Classified Cases in Ranges")
        .AutoSort xlDescending, "Count of PR ID"
    End With
    
    ' Add labels and formatting
    PSheet.Range("B2").Value = "Total Backlog"
    PSheet.Range("B3").Value = "Days"
    
    With PSheet.Range("B2")
        .Interior.ColorIndex = 4
        .Resize(1, 2).Merge ' Merge B2:C2 cleanly
    End With
    PSheet.Range("B3:C3").Interior.ColorIndex = 4
End Sub

Final Checks #

• Manually update your PC Excel Professional Plus (File > Account > Update Options) to ensure you have the latest bug fixes.
• Confirm your "US Master Macro" sheet has data starting at cell A1 (your code uses this as the pivot source).
• Use the VBA debugger (press F8 to run line-by-line) to catch any remaining issues in real time.

内容的提问来源于stack exchange，提问作者Myriam Diaz Martinez