Pivot Table from Macro in Excel

[kpkelley]

I'm trying to create a macro that will output a pivot table on the same sheet as the data from which the pivot table was created. I'm running in to a few errors.

Firstly, I get an error message every time I try to run the macro and it highlights this sections as being the culprit:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"Report 005!R1C1:R100000C41", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Report 005!R2C53", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion12

I can bypass the issue by changing TableDestination from "Report 005!R2C53" to "", but then the pivot table gets created in another sheet. Do you know of an alternative?

The other issue that I've encountered is that some of the changes I made to the Pivot Table when I was recording it for one set of data, cause errors on the second second set of data. On the first data set I deselected several instruments from the "instrument" row in the pivot table as seen in this code:
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Instrument")
.PivotItems("C").Visible = False
.PivotItems("D").Visible = False
.PivotItems("R").Visible = False
.PivotItems("(blank)").Visible = False.
End With

However, the second data set doesn't have any Instrument type D data points. Is there a way to avoid the error message if a data set doesn't have that Instrument Type?

TIA

[Anthony P]

I think your first issue is a simple one. If you recorded the macro (and from what you pasted it looks like you did) than a pivot table already exists at Report 005!R2C53 so you would need to delete the one that is there before you begin that is why it lets you create it on a new page since there is never a pivot table on a blank new page

for the second one the easiest thing to do is

With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Instrument")
On error resume next
.PivotItems("C").Visible = False
.PivotItems("D").Visible = False
.PivotItems("R").Visible = False
.PivotItems("(blank)").Visible = False.
On error goto 0
End With

in essence the first bold line means that if there is an error just skip that line and in this case it makes sense, no use hiding data that is not there in the first place. It is also good to stop it right after so if there are other errors they will get traped

[kpkelley]

Thanks for the response.

I'll try the second one shortly, but the first one isn't the problem that I'm running into. I've deleted all the data on the sheet and replaced it, including taking out the pivot table.

One idea that I saw on another forum, was that it had to do with the pivot table name. Since a pivot table of that name already existed, it couldn't create another one. The solution was a string integer with the pivot table name, but I don't know how to write that.

TIA

[kpkelley]

I got it to work in a new sheet with the on error resume, by changing the TableDestiantion to "".

Thanks.