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