Hey all,
I have a reference page at the end of my workbook which contains
multiple pivot table each of which reflect data from a corresponding
worksheet i.e. the first pivot table reflects data from worksheet 01,
the second gets data from worksheet 02 etc.
I am looking at finding a way to generate these pivot tables
automatically as at the minute whenever I add a new worksheet I then go
to my reference page and copy an existing pivot – paste it and then use
the pivot table wizard to select the correct sheet/ range.
When I recorded the process I got this code:
Sheets(“Reference”).Select
Range("AB1:AC19").Select
Range("AC19").Activate
Selection.Copy
Range("AE1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AE4").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=
_
"'04'!R8C1:R463C1"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _
"Agent Number/Name"
Can anyone think of a way to automate this? I am thinking it would
involve counting the sheets and updating the formulae
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'04'!R8C1:R463C1"
to look at a variable ‘sheets’ rather then an actual sheet name (in
that case 04).
Hope this all makes sense and thanks for any help.
Hayley
--
Hru48
------------------------------------------------------------------------
Hru48's Profile:
http://www.excelforum.com/member.php...o&userid=24895
View this thread:
http://www.excelforum.com/showthread...hreadid=558120