Macro to Pivot data in each sheet within a workbook
in [Programming]
|
From: sidm via OfficeKB.com on 14 Apr 2010 10:12 Hi , What code would I need to create an individual Pivot for data in each sheet within a workbook , all sheets have the same headings but different number of rows and all pivots will be the same . or If I create a master pivot from the data in all the worksheets , could I break it up so that each page field is a seperate tab showing the relevant Pivot. Thanks, sidm -- Message posted via http://www.officekb.com
From: ryguy7272 on 14 Apr 2010 11:32 Ok, sounds like you are kind of new to VBA. No problem at all, just be aware of the fact that this will take a bit of work. It's very gratifying when you figure it out though!! Try recording a macro as you build one Pivot Table. Here's some info. about macro-recording: http://www.anthony-vba.kefra.com/vba/vbabasic1.htm Look at your code. Compare your code to my code, below: Sheets("MergeSheet").Select Cells.Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ Sheets("MergeSheet").Range("A1").CurrentRegion).CreatePivotTable _ TableDestination:="", TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select Notice, my sheet is named 'MergeSheet' Pay attention to this part: ..Range("A1").CurrentRegion) You'll need a For...Next loop too. See a sample here: http://www.mrexcel.com/archive/VBA/24967.html Post back with specific questions. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "sidm via OfficeKB.com" wrote: > Hi , > What code would I need to create an individual Pivot for data in each sheet > within a workbook , all sheets have the same headings but different number of > rows and all pivots will be the same . > or > If I create a master pivot from the data in all the worksheets , could I > break it up so that each page field is a seperate tab showing the relevant > Pivot. > Thanks, > sidm > > -- > Message posted via http://www.officekb.com > > . >
From: sidm via OfficeKB.com on 14 Apr 2010 11:46 Hi , Thanks for the reply, will try out the process and get back. sidm ryguy7272 wrote: >Ok, sounds like you are kind of new to VBA. No problem at all, just be aware >of the fact that this will take a bit of work. It's very gratifying when you >figure it out though!! Try recording a macro as you build one Pivot Table. >Here's some info. about macro-recording: > >http://www.anthony-vba.kefra.com/vba/vbabasic1.htm > >Look at your code. Compare your code to my code, below: > >Sheets("MergeSheet").Select > > Cells.Select > ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ > Sheets("MergeSheet").Range("A1").CurrentRegion).CreatePivotTable _ > TableDestination:="", TableName:="PivotTable1", _ > DefaultVersion:=xlPivotTableVersion10 > > ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) > ActiveSheet.Cells(3, 1).Select > >Notice, my sheet is named 'MergeSheet' > >Pay attention to this part: >.Range("A1").CurrentRegion) > >You'll need a For...Next loop too. See a sample here: >http://www.mrexcel.com/archive/VBA/24967.html > >Post back with specific questions. > >> Hi , >> What code would I need to create an individual Pivot for data in each sheet >[quoted text clipped - 6 lines] >> Thanks, >> sidm -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-programming/201004/1
|
Pages: 1 Prev: copy rows with values - not formulas Next: Active.Paste Error |