Run time error 1004 when creating Pivot chart from macro

Question

I have written a macro to add a pivot chart in my Excel. I am getting an

Application-defined or object-defined error

in the following line. Can anybody help me with this?

Set DSheet = ActiveWorkbook.Worksheets("Sheet1")
Worksheets("Sheet1").Activate
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)

Table1_Start_Line = 2
Table1_End_Line = Table1_Start_Line + LastRow
Column_Line = LastCol + 2
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange, Version:=xlPivotTableVersion15).CreatePivotTable TableDestination:=DSheet.Cells(Table1_Start_Line, Column_Line), TableName:="PivotTable", DefaultVersion:=xlPivotTableVersion15

Take a look here: https://stackoverflow.com/questions/12861909/how-to-create-a-pivot-table-in-vba — Variatus, Jan 30 '19 at 10:40
I would suspect this line of your code `TableDestination:=DSheet.Cells(Table1_Start_Line, Column_Line)`. Inserting a pivot table into a table may not be acceptable to Excel. However, no definite answer to your question can be given without looking at the ranges and variables declared before you come to the error line. The item I highlight is the only one I can see. If it doesn't cause the error then the source of the error must be in the previous declarations. — Variatus, Jan 30 '19 at 10:46
@Variatus I tried that way also. But getting the same error. — NAVEEN PRAKASH, Jan 30 '19 at 11:17
Your code works for me (vers 14 instead of 15 for Excel 2010) but I get the same error if I try to run it before deleting the PT previously created. Are you sure your Destination range is free and available? — Variatus, Jan 30 '19 at 11:51

score 0 · Answer 1 · answered Jan 30 '19 at 12:07

I think this code will do what you want. In essence, it deletes an existing table before inserting a new one.

Private Sub RenewPivotTable()

    Dim DSheet As Worksheet
    Dim PTable As PivotTable
    Dim LastRow As Long, LastCol As Long
    Dim PRange As Range
    Dim Table1_Start_Line As Long, Column_line As Long

    Set DSheet = ActiveWorkbook.Worksheets("Sheet1")
    With DSheet
        If .PivotTables.Count Then
            .PivotTables(1).TableRange2.Clear
        End If
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Set PRange = .Cells(1, 1).Resize(LastRow, LastCol)
    End With

    Table1_Start_Line = 2
    Column_line = LastCol + 2
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                      SourceData:=PRange, _
                                      Version:=xlPivotTableVersion15).CreatePivotTable _
                                      TableDestination:=DSheet.Cells(Table1_Start_Line, Column_line), _
                                      TableName:="PivotTable", _
                                      DefaultVersion:=xlPivotTableVersion15
End Sub

I tested the Version14 version of it in Excel 2010

Run time error 1004 when creating Pivot chart from macro

1 Answers1