Macro acting up on another computer doing pivot table

jinme1981 

It sounds like the Pivot Table layout is not being replicated consistently on another computer due to differences in Excel settings or environment configurations. Here's a possible solution to ensure that the Pivot Table layout behaves consistently across different computers when running your macro.

Potential Issues:

1. Pivot Table Version Differences: The Version:=8 in your macro indicates that you're using an older pivot table version, which could cause compatibility issues on other computers.
2. Pivot Table Field Settings Not Being Applied: It's possible that even though your macro specifies certain field settings, they might not persist on other computers due to user or Excel environment settings.
3. Locale or Regional Settings: Differences in date or number formatting between computers can sometimes cause problems when working with pivot tables.

Solution:

You can modify your macro to explicitly set the field layout options and ensure consistent field arrangements across computers. Below is an updated version of your macro with explicit layout settings and updated version compatibility.

Updated Macro:

Vba Code is untested, please backup your file beforehand.

Sub CreatePivotTable()
    Dim LastRow As Long

    ' Assuming you already calculate the last row dynamically somewhere above.
    ' Here is the structure of the Pivot Table creation
    
    ' Create Pivot Table
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Wall Panel_1!R2C1:R" & LastRow & "C9", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="Pivot!R3C1", TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion15
    
    ' Select the Pivot sheet
    Sheets("Pivot").Select
    Cells(3, 1).Select
    
    ' Refresh and set missing items limit
    With ActiveSheet.PivotTables("PivotTable3").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsNone ' Change this if needed
    End With
    
    ' Set the display to Tabular Form and Repeat Labels
    With ActiveSheet.PivotTables("PivotTable3")
        .InGridDropZones = False ' Ensure consistent layout
        .RowAxisLayout xlTabularRow
        .RepeatAllLabels xlRepeatLabels
    End With
    
    ' Set Panel Location to Row Field
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("PANEL LOCATION")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    ' Add Data Field for "COUNT"
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("COUNT"), "Sum of COUNT", xlSum
    
    ' Set Panel Length to another Row Field
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("PANEL LENGTH")
        .Orientation = xlRowField
        .Position = 2
    End With
    
    ' Optional: Set other pivot field properties or sorting as needed
End Sub

Key Changes and Explanation:

1. Pivot Table Version:
• I've changed Version:=8 to Version:=xlPivotTableVersion15 (which corresponds to Excel 2013 and above). This helps maintain better compatibility across newer Excel versions.
• If you want to ensure maximum compatibility with Office 365 or newer versions, use xlPivotTableVersionCurrent.
2. Row Layout Settings:
• .RowAxisLayout xlTabularRow forces the Pivot Table to display in tabular form.
• .RepeatAllLabels xlRepeatLabels ensures that labels are repeated where necessary.
3. Explicit Missing Items Limit:
• Setting .MissingItemsLimit = xlMissingItemsNone ensures there are no extra cached items that might cause layout inconsistencies.
4. Field Positioning:
• .Orientation = xlRowField and .Position explicitly control the location of each field, making sure the layout remains the same regardless of Excel environment.

Additional Considerations:

• Locale Differences: Ensure both computers are using similar regional settings. Sometimes the default behavior of Pivot Tables can differ due to date or currency formatting.
• Excel Version Differences: Ensure both computers are using the same or compatible versions of Excel for consistency.

By explicitly setting these layout options in your macro, you should achieve consistent results across different machines. The text and the code was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.