Error "Subscript out of range"

[Biel]

Hello Evrybody,

I'm making a little test to filter from a cell value another workbook with all lot of pivot table.
I make a little test and create the vba but it's keep giving to me the same error but the references to pivottable and sheet is ok.

I hope you can help

Stay safe!!

 

[Anthony47]

You use the wrong "doublequote" for the sheet and pivottable name
Also, since the code in in Sheet2 code module, you can use "Me".

Set pt = Me.PivotTables("PivotTable1")

Bye

 

[Biel]

Omg! S

 

[Biel]

Such and stupid error!! Thanks so much

 

[Anthony47]

Don't worry, you will certainly have more complicated problems in the future

 

[Biel]

Hello,

Sorry, but when I tried to the original file with all the pivot table it's not working.
I have another problem "Run Time Error 1004 'Unable to get the PivotFields property of the PivotTable class"

What I want to do is filter 2 fields from value coming from a cell of the different Pivot table in the sheet. (All pivot table has the same fields)

Thanks so much for you help, I'm new with that and I'm learning.

I appreciate a lot.


Biel Bulbena

 

[Domenic]

It looks like you're missing an 's' in PivotFields...

Set Field = pt.PivotFields("Turno")

Hope this helps!

 

[Biel]

Yes I seen it afterwards but I have the same problem

 

[Anthony47]

Are you sure that the table you are pointing to is named PivotTable4?
Try using

Set pt = Me.Range("AM1").PivotTable
Debug.Print pt.Name
See Fields =pt.PivotFields("Turno")

If it does work, then open the vba "Immediate window" and you will see the actual name of that pivot; to open the Immadiate window, from vba press Contr-g, or use Menu /View /Immediate window.
And, going further in your code, be aware that your With pt /End With structure is useless, as you never refer (in the 3 instructions within the structure) the object set by the With statement; it is not harmful, its useless and you may remove the two lines /( With pt and End With)

Bye

 

[Biel]

Hello,

Yes as you can see in the imatge in the second post, the pivot table name is correct.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("AG3:AG4")) Is Nothing Then Exit Sub

Dim pt As PivotTable
Dim Field As PivotField
Dim Cat As String

Set pt = Me.Range("AM1").PivotTable
Debug.Print pt.Name
See Fields = pt.PivotFields("Turno")

Field.ClearAllFilters
Field.CurrentPage = Cat
pt.RefreshTable

End Sub

This its not working

Thanks a lot for the help