Macro to Pivot data in each sheet within a workbook

[sidm via OfficeKB.com]

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

 

[ryguy7272]

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.

 

[sidm via OfficeKB.com]

Hi ,
Thanks for the reply, will try out the process and get back.
sidm

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