Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Pivot data in each sheet within a workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Pivot data in each sheet within a workbook
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:=xlDatab ase, SourceData:= _ Sheets("MergeSheet").Range("A1").CurrentRegion).Cr eatePivotTable _ 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 he 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Pivot data in each sheet within a workbook
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:=xlDatab ase, SourceData:= _ Sheets("MergeSheet").Range("A1").CurrentRegion).Cr eatePivotTable _ 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 he 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.a...mming/201004/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy specific data from master workbook to another workbook | Excel Programming | |||
Help needed: Getting all sheet setup data from one workbook toanother workbook | Excel Programming | |||
Aquiring data from one sheet to another sheet in the same workbook | Excel Worksheet Functions | |||
Help to sort out filtered data from the data contained in another sheet of the same workbook | Excel Worksheet Functions | |||
Copying data from workbook/sheets to another workbook/sheet | Excel Programming |