Creating multiple pivot tables on one sheet | Excel forum

Hi,
I am trying to create two pivot tables on one worksheet from the same data source. When i create the second pivot table, changing the destination and table name, the first pivot table is just moved to the new location.

many thanks

Sam

my code is

Sub MakePivots()



Dim DataRange As Range

Dim Destination As Range





' set data range for pivot tables

Set DataRange = Worksheets("Report").Range("a1:bo41")



'set destination for 1st pivot table

Set Destination = Worksheets("numbers").Range("A11")



'create 1st pivot table

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _

SourceData:=DataRange, TableDestination:=Destination, TableName:="PagesByDay"

 

'add row and data fields

With Sheets("numbers").PivotTables("PagesByDay")

          .PivotFields("Page Created Date").Orientation = xlRowField

          .PivotFields("Fundraiser User Id").Orientation = xlDataField



End With





'set destination for second pivot table

Set Destination = Worksheets("numbers").Range("g11")



'create second pivot table - destination and name are differnet to 1st pivot table

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _

SourceData:=DataRange, TableDestination:=Destination, TableName:="PagesByWeek"



'add row and data fields for 2nd pivot table

With Sheets("numbers").PivotTables("PagesByWeek")



          .PivotFields("Page Created Date").Orientation = xlRowField

          .PivotFields("Fundraiser User Id").Orientation = xlDataField



End With

End Sub