• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

.CurrentPage issues

Qhawe

New Member
Good day all,
VBA stops running and gives me an error 1004 message at the CurrentPage statement on the code.
What I am attempting to do here is to be able to filter the type report filter from a separate page, so this is just the set up for that getting cell d2 and cell e2 to filter the pivot table. I am using these two cell so that if I what to see shop 1 and 2 data at the same time I can do that.
Please help.
Code:
If Intersect(Target, Range("d2", "e2")) Is Nothing Then Exit Sub

Dim WW As PivotTable
Dim Field As PivotField
Dim Qa As String
Dim Sec As String



Set WW = Worksheets("NFIFILTER").PivotTables("PivotTableN")
Set Field = WW.PivotFields("Type")
Qa = Worksheets("NFIFILTER").Range("d2").Value
Sec = Worksheets("NFIFILTER").Range("e2").Value



With WW
Field.ClearAllFilters
Field.CurrentPage = Qa
Field.CurrentPage = Sec
WW.RefreshTable
End With

End Sub



With WW
Field.ClearAllFilters
Field.CurrentPage = Qa
Field.CurrentPage = Sec
WW.RefreshTable
End With

End Sub
 

Attachments

  • Que.xlsm
    63.6 KB · Views: 0
Check this & let us know!!

Code:
With WW.Field
.ClearAllFilters
.CurrentPage = Qa
.CurrentPage = Sec
WW.RefreshTable
End With

Don't forget to share sample file.
 
Thank you Deepak, however it is still giving me the same issue. I have attached the file below.
 

Attachments

  • Que.xlsm
    63.6 KB · Views: 2
Thank you Narayan, this was very helpful, the code works well.
Can you please explain to me this part of the code
Code:
For i = 1 To .PivotItems.Count
                          If .PivotItems(i) <> Qa And .PivotItems(i) <> Sec Then
                            .PivotItems(i).Visible = False
                          End If
                      Next i
                End With

Qhawe
 
Hi ,

What the code is doing is within the report filter , it is unchecking those items which are neither Shop 1 ( Qa ) nor Shop 2 ( Sec ).

Narayan
 
Back
Top