Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic pivots
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic and manual calculations | Excel Discussion (Misc queries) | |||
enable automatic refresh | Excel Worksheet Functions | |||
Please help with adding automatic data. | Excel Discussion (Misc queries) | |||
options to update automatic links | Excel Worksheet Functions | |||
How can I find automatic links in an Excel-sheet? | Excel Discussion (Misc queries) |