Run-time Error 1004: Application-defined or Object-defined Error

[Adrian]

Hi, Can anyone help me. I get this message when using the Macro's and I'm not
sure how to fix... Below is a snippet of code - The error occurs on the last
three lines of code... Any suggestions?

Thanks

Sheets.Add
Sheets.Add
Sheets("Sheet1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R25000C37").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _
"StatusDescription"
ActiveSheet.PivotTables("PivotTable1").PivotFields("StatusDescription"). _
Orientation = xlDataField
With
ActiveSheet.PivotTables("PivotTable1").PivotFields("StatusDescription")
.PivotItems("(blank)").Visible = False
End With
Sheets("Sheet1").Select

ActiveWorkbook.Worksheets("Sheet4").PivotTables("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:="", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10

 

[Rowan]

When you recorded this macro the PivotTable was created on a new sheet called
Sheet4. If you delete this sheet and run the macro again the PivotTable is
created on a new sheet probably called Sheet5 but definately not called
Sheet4.

So when you reference Sheet4 in the last statement of your macro it fails.

One way to fix this is to name the sheet created for the PivotTable and then
use that name in the rest of the macro, something like this:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R25000C37").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
Activesheet.Name = "MyPivot"
'rest of code
ActiveWorkbook.Worksheets("MyPivot").PivotTables("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:="", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10

Hope this helps
Rowan

 

[Adrian]

Hi Rowan,
Thanks for the response but the error still occurs at the same point even if
the sheet has been named...

Any other Idea's?

 

[Rowan]

Hi Adrian

I should have forseen that. You need to repeat the line:

Sheets("Sheet1").Select

just before the statement to create the second pivot table (the one causing
the error).

Also the code:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("StatusDescription")
.PivotItems("(blank)").Visible = False
End With

will cause an erorr if there is no blank data so you may want to wrap an
errorhandler around it:

On Error Resume Next
With ActiveSheet.PivotTables("PivotTable1").PivotFields("StatusDescription")
.PivotItems("(blank)").Visible = False
End With
On Error Goto 0

(watch out for the line wrap in the two examples above).

Regards
Rowan

 

[Adrian]

Hi Rowan,

Still doesn't work... However I think I know why...

After the first table is created and the second table is created a message
pops up asking if you want to use the same data as the first table to reduce
the file size. I believe this is causing the problem because if I click No
when it asks you if you want to reduce the file size everything works
perfectly. So I guess I may not be able to do exactly what I would like...

Thanks for the help...

 

[Rowan]

Hi Adrian

The final statement in the macro is what you get when you respond yes to the
question of if you want to use the same data for the new pivot table so it
should work.

My modified code which works looks like this:

Sheets("Sheet1").Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase _
, SourceData:="Sheet1!R1C1:R2500C37").CreatePivotTable _
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

ActiveSheet.Name = "MyPivot"
ActiveSheet.PivotTableWizard TableDestination:= _
ActiveSheet.Cells(3, 1)
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _
"StatusDescription"
ActiveSheet.PivotTables("PivotTable1"). _
PivotFields("StatusDescription").Orientation = xlDataField
On Error Resume Next
With ActiveSheet.PivotTables("PivotTable1"). _
PivotFields("StatusDescription")
.PivotItems("(blank)").Visible = False
End With
On Error GoTo 0

Sheets("Sheet1").Select

ActiveWorkbook.Worksheets("MyPivot").PivotTables("PivotTable1"). _
PivotCache.CreatePivotTable TableDestination:="", _
TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion10


Regards
Rowan

 

[Adrian]

Hi Rowan,

I'm sorry. I don't know if it's me but it still gets the error and point's
to the below section of the code...

ActiveWorkbook.Worksheets("MyPivot").PivotTables("PivotTable1"). _
PivotCache.CreatePivotTable TableDestination:="", _
TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion10

Have you any other idea's? If not I think I might try what I want another way.

Thanks