How to run more than one pivot table at a time in a macro

[rfarnlof]

I have created two separate macros. The first creates 6 different new sheets with data from a master, then on each sheet a pivot table is created to summarize the data on that sheet. Subsequently, When I try to create a new sheet with a new pivot table to copy data from each of the individual pivots. Here is a snippet of code that creates the smaller pivots:

[pre]

Sheets("MG6A-MF").Activate
Range("Z2").Select
ActiveSheet.PivotTableWizard xlDatabase, Range("A:O")
'ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
With ActiveSheet.PivotTables("PivotTable1").PivotFields("LOB")
.Orientation = xlRowField
.Position = 1
End With

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Application ID"), "Count of Application ID", _
xlCount

Now when I try to create another new sheet pivot table with this code


Sheets("owssvr").Activate
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table_owssvr", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable26", DefaultVersion _
:=xlPivotTableVersion12
Sheets("Sheet1").Select

[/pre]
I receive an error Run-Time error 5 "Invalid procedure call or argument"


I have made this one macro, now it is two macros. If I save it and close excel after running the first macro, then opening it again I can then successfully run the second macro

 

[vasim]

I am not sure if you have pasted the whole code (as you say add 6 sheets but no where in the code this conveys).


Assuming this, do you change the new sheet name, if yes, have you deleted the older sheet with the same name..Excel cannot create two tabs with same name..

 

[rfarnlof]

Thank You for the response, however, yes I make sure that no two Pivot Tables have the same name..I have as follows..PivotTable1,PivotTable2,PivotTable3,PivotTable4,PivotTable5,PivotTable6 and PivotTable26