Attn: Barb Reinhardt
 

Barb, I am reposting this today in an attempt to contact you. I am assuming
the discussion board does not monitor old strings. If your still out there, I
hope we can get back on this.

I 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

"Barb Reinhardt" wrote:

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
--
HTH,
Barb Reinhardt