Error Creating Pivot Table

[jkwleisemann]

Well, I'm trying to create a pivot table from raw data on a weekly report (technically, a co-worker is, and I'm trying to help them debug the macro). Everything seems to be reformatting on the source report correctly, but the problem happens when I'm creating the pivot table. I'm sharing the code here, with a comment line that indicates where the Debug puts me. I'm not that familiar with creating pivot tables in VBA, so I'm hoping the gurus here will be more useful for my co-worker!

Sub Delta_Dental()
'
' Delta_Dental Macro
' Formatting for Weekly Delta Dental Invoice to get Pivot Table needed.
'
'
    Range("M3").Select
    ActiveCell.FormulaR1C1 = "AMOUNT PAID"
    Range("M4").Select
    Columns("K:K").EntireColumn.AutoFit
    Columns("K:K").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("K5").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)"
    Range("K5").Select
    Selection.Copy
    Range("K5:K3418").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("K3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "CY"
    Range("K5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Columns("K:K").Select
    Application.CutCopyMode = False
    Selection.Cut
    Range("X1").Select
    ActiveSheet.Paste
    Columns("K:K").Select
    Selection.Delete Shift:=xlToLeft
    Range("A3:W3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A3:W10253").Select
    Sheets.Add
    
    'Error seems to happen here - result 1004, application/user defined error, so of course it's easy to identify!
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Worksheets(2).Range("R3C1:R10253C23"), Version:= _
        xlPivotTableVersion15).CreatePivotTable TableDestination:=Worksheets(1).Cells(3, 1), _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion15
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("AMOUNT PAID")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("GROUP#")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("SUB")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("CY")
        .Orientation = xlRowField
        .Position = 4
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("AMOUNT PAID"), "Count of AMOUNT PAID", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of AMOUNT PAID")
        .Caption = "Sum of AMOUNT PAID"
        .Function = xlSum
        .NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("GROUP#")
        .PivotItems("(blank)").Visible = False
    End With
End Sub

 

[SpillerBD]

If there is already a Pivot Table named "PivotTable1" it will error because you can't Add when it already exist.
This is the code I use to check if a SheetName already exist and delete before proceeding to create the sheet again. You need to do the same for the PivotTable name.

MySheetName1 = "BU Summary"
On Error Resume Next 'skip the error if the name doesn't exist
    MyName = ActiveWorkbook.Worksheets(MySheetName1).Name
    If Err.Number = 0 Then
        NameExists = True
            Sheets(MySheetName1).Delete 'Remove existing sheet
            Worksheets.Add.Name = MySheetName1
            
            Else
        NameExists = False
            Worksheets.Add.Name = MySheetName1
    End If

 

[jkwleisemann]

A part of me wants to say that it's not possible for that to be the problem, because there are no other pivot tables, but I know better than to assume Excel will be logical.

However, on going in to test it, I've found that I've got good news - the "On Error Resume Next" is causing it not to error out - and bad news.

Namely, that it still fails to create the pivot table.

So I think there's something wrong in my CreatePivotTable function, but I'm not sure what. The name seems not to be the problem, since it's checking a worksheet that was freshly created before it tries to create a new sheet.

 

[SpillerBD]

I do remember my frustrations in creating the VBA for PT creation.
For the source Range, I went with a defined named range. Make sure that the range you're using includes headers for every column.
For the target sheet I also went with defining that with a created sheet. That's why I had the Sheet Name check procedure I previously copied to thread.
Other than that, that specific lines syntax appears exactly like mine.
Mine being for Excel 2013

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "ElavonData", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="ePivot!R1C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion15

 

[jkwleisemann]

I do remember my frustrations in creating the VBA for PT creation.
For the source Range, I went with a defined named range. Make sure that the range you're using includes headers for every column.
For the target sheet I also went with defining that with a created sheet. That's why I had the Sheet Name check procedure I previously copied to thread.
Other than that, that specific lines syntax appears exactly like mine.
Mine being for Excel 2013

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "ElavonData", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="ePivot!R1C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion15

Well, thanks for confirming that the syntax is correct at least.

Aaaaaaaand I feel like an idiot now!

I think the problem was right here: Worksheets(2).Range("R3C1:R10253C23")

That had it trying to find a range with a cell reference that should've thrown some more specific error (seriously, would it kill Microsoft to add a "What are you trying to say?" error?)

I switched it to the translated reference (A3:W10253) and it went through clean.