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" wrote:

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" wrote:

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" wrote:

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:=xlDatab ase, 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" wrote:

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:=xlDatab ase, 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("P ivotTable1").PivotCache. _
CreatePivotTable TableDestination:="", TableName:="PivotTable2", _
DefaultVersion:=xlPivotTableVersion10