Pivot Table Macro

[Looping through]

I have created a PT that seperates a bunch of information out of a master
log. In the "Page Field" of the table I have the whole list filtered down by
a specific persons name. There is roughtly 30 names possible in this field
and I want to be able to run a macro that selects the top name in the range
(this will filter my PT) > copy all the active information on the sheet >
open a new workbook > paste the values I just copied and save. Then I want to
go back to my original PT and open up the "Page Field" again and select the
next active name and repeat until I have gone thru all active names.

Can this be done?
Any help is Greatly Apprechiated.
Peter

 

[Barb Reinhardt]

Can you do this for me? Perform the function you want done for the first
name and record that as a macro. Then come back here and post what you have
and we can help clean it up.

 

[Looping through]

this is what came out of the recorder after selecting the top two names

Sub Filter_Rep()

ActiveSheet.PivotTables("PivotTable1").PivotFields("Rep.").CurrentPage = _
"01 - Power Reps"
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Cells.Select
Windows("Quote log 2007.xls").Activate
ActiveSheet.PivotTables("PivotTable1").PivotFields("Rep.").CurrentPage = _
"02-AC & DC Power Tech"
Cells.Select
ActiveSheet.Paste
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Thanks
Peter

 

[Barb Reinhardt]

I can't guarantee that this will work, as I don't have your exact pivot
table, but try it on a copy of your workbook. You need to run it from the
sheet with the pivot table.

 

[Barb Reinhardt]

Oops, try this

Sub Filter_Rep()
Dim aWS As Worksheet
Dim newWS As Worksheet
Dim myWB As Workbook
Dim myWS As Worksheet
Dim myPivotItem As PivotItem

Set aWS = ActiveSheet

For Each myPivotField In aWS.PivotTables("PivotTable1").PivotFields
Debug.Print myPivotField.Name
If myPivotField.Name = "Rep." Then
For Each myPivotItem In myPivotField.PivotItems
Debug.Print myPivotItem.Value
If Not myPivotItem.Value = "(blank)" Then
myPivotField.CurrentPage = myPivotItem.Value
Set myWB = Workbooks.Add
Set myWS = myWB.Worksheets(1)
myWS.Range(aWS.UsedRange.Address) = aWS.UsedRange
Debug.Print aWS.UsedRange.Address
aWS.UsedRange.Copy
myWS.PasteSpecial
End If
Next myPivotItem
End If
Next myPivotField
End Sub

 

[Looping through]

Try what?

I can't guarantee that this will work, as I don't have your exact pivot
table, but try it on a copy of your workbook. You need to run it from the
sheet with the pivot table.

 

[Looping through]

Barb, Happy new year.

I have added this code to my workbook and tried it. I get a run time error
1004 (Unable to set the _Default property of the Pivotitem class) when I
debug the line of code highlighted is "myPivotField.CurrentPage =
myPivotItem.Value"

thanks for your help to this point.
Peter