Hi,

I have created a pivot table grouping data as follow

Item No Country Sales_person Oct_SUM
======= ======= ============ ==========
1 US John 345
Peter 354

UK Andy 235

FR Fred 255

2.....

3.....

4.....

....

I would like to know if it's possible to write some VB
codes to automate the process of creating a seperate
sheet/workbook for each item_no which will include the
other fields (country, sales_person and Oct_SUM)

Thanks a lot!

Cary

If you move Item No to the Page area, you can use the following code to
create a sheet for each item:

'======================
Sub ExtractPivotPages()
'extracts data for each page field
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim wsPivot As Worksheet
Dim ws As Worksheet
Set wsPivot = Worksheets("Pivot")

Set pt = wsPivot.PivotTables(1)
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
pt.PivotFields(pf.Name).CurrentPage = pi.Name
Set ws = Worksheets.Add
ws.Name = pi.Name
wsPivot.UsedRange.Copy
ws.Range("A1").PasteSpecial xlPasteValues
ws.Range("A1").PasteSpecial xlPasteFormats
Next
Next pf

End Sub
'==================================

Cary wrote:
Hi,

I have created a pivot table grouping data as follow

Item No Country Sales_person Oct_SUM
======= ======= ============ ==========
1 US John 345
Peter 354

UK Andy 235

FR Fred 255

2.....

3.....

4.....

...

I would like to know if it's possible to write some VB
codes to automate the process of creating a seperate
sheet/workbook for each item_no which will include the
other fields (country, sales_person and Oct_SUM)

Thanks a lot!

Cary


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html