Pivot Macro Error

[MHamid]

Hello,

I am not sure what is going on, but a colleague of mine is getting an error message when running the macro I created to create a pivot table.

'Declare Variables to hold source cell range address
    Dim mySourceData As String
'Declare Variables to hold references to source and destination worksheets and Pivot Table
    Dim SourceSheet As Worksheet
    Dim DestSheet As Worksheet
'Declare Variales to hold row and column numbers that will define source data cell range
    Dim FirstRow As Long
    Dim lastRow As Long
    Dim FirstCol As Long
    Dim LastCol As Long
'Declare Variable to hold name for pivot table cache and pivot table
    Dim PCache As PivotCache
    Dim PTable As PivotTable
    Dim PTable2 As PivotTable
    
'Set/Define Source and Destination Variables
    With ThisWorkbook
        Set SourceSheet = .Worksheets("Audit_Plan")
        Set DestSheet = .Worksheets("OTRC MOR File")
    End With
'identify first row and first column of source data cell range
    FirstRow = 6
    FirstCol = 1
'Find last row and last column of source data cell range
'And obtain address of source data cell range
    With SourceSheet.Cells
        lastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        LastCol = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        mySourceData = .Range(.Cells(FirstRow, FirstCol), .Cells(lastRow, LastCol)).Address(ReferenceStyle:=xlR1C1)
    End With
'Conditional based on "Watchlist" being listed in Audit Plan
    Dim rng As Range
    Dim rngFound As Range
    
    Set rng = Worksheets("Audit_Plan").Range("BA:BA")
    Set rngFound = rng.Find("Yes")
    
        If rngFound Is Nothing Then
                'Add Prior Month Date
                Range("BG1").Select
                    With Selection
                        .FormulaR1C1 = "=EOMONTH(TODAY(),-1)-DAY(EOMONTH(TODAY(),-1))+1"
                        .NumberFormat = "mm/dd/yyyy"
                        .Interior.Pattern = xlSolid
                        .Interior.PatternColorIndex = xlAutomatic
                        .Interior.Color = 65535
                        .Interior.TintAndShade = 0
                        .Interior.PatternTintAndShade = 0
                    End With
                    
                Range("BG1").Copy
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                Application.CutCopyMode = False
            
            'Insert Pivot Table ("Audit_Plan!R6C1:R1048576C93")
                    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                    SourceSheet.Name & "!" & mySourceData, Version:=6).CreatePivotTable TableDestination _
                    :=DestSheet.Cells(4, 60), TableName:="AdditionalItemsTable", DefaultVersion:=6

This is where the error occurs:

            'Insert Pivot Table ("Audit_Plan!R6C1:R1048576C93")
                    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                    SourceSheet.Name & "!" & mySourceData, Version:=6).CreatePivotTable TableDestination _
                    :=DestSheet.Cells(4, 60), TableName:="AdditionalItemsTable", DefaultVersion:=6

Why would it work in my instance but not my colleague?

Thank you

 

[pbornemeier]

I see a few possibilities:
1) Earlier version of Excel that does not like the Version/Default Version Number
2) Target cell on Dest sheet is part of a merged cell
3) Pivot Table already exists at Dest sheet cell
4) Data block has a blank cell in the first row (verify mySourceData address)

#4 may be the most likely:

I created a table in A6:R43 and ran your code. mySourceData was R6C1:R43C18. The code worked.

I ran it a second time. mySourceData was R6C1:R43C59. The code failed with error 1004 and an error description starting with: "The pivot table field name is not valid....

In the second running of the code the statement: LastCol = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
found the cell (BG1) where the initial run of the code added a date.

So in my case the columns after column R and before column BG had no headers, and the code errored out. Expanding my table past column BG eliminated that problem.

I expect your colleague's data did not contain sufficient columns to include column BG, so the empty columns and consequently blank titles caused the code to fail.

I generally use this code to determine PT Source data size:

sSourceSheet = Audit_Plan
sSourceULCell = "A6"

mySourceData = sSourceSheet & "!" & ActiveWorkbook.Worksheets(sSourceSheet).Range(sSourceULCell). _
CurrentRegion.Address(ReferenceStyle:=xlR1C1)