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

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

.

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