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