Change the order of Pivot tables in same worksheet

Question

I have multiple pivot tables on one same worksheet (from left to right : PvtTable 1, Pvttable 2, Pvttable3). I have introduced a iterator (i) to call them one after the other (msgbox their name). However, when i run the macro, the names are in an illogical order : they don't display pvttable1 then 2 then 3 nor backwards (3,2,1). It displays pvttable2, pvttable1, pvttable3. Is there a tool that wold help me initiate and tell VBA which one to call first ? What is this order based on ? Simple piece of code I use that returns a weird order :

For i = 1 to 3 
    MsgBox ActiveSheet.PivotTables(i).Name
Next i

*my pivot tables have changing string names btw, the names mentioned are just examples to help make understand they don't appear in order

Thanks everyone, hope it was clear!

What is this order based on ? As far as I know, the order is determined by which Pivot Table was first created in the file. — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Jul 6, 2022 at 12:28
You cannot change the order once the pivot tables are created. But you can call them by their names like ActiveSheet.PivotTables("pvttable" & i), or you can read all the names in a dictionary first and then sort the dictionary. — Pᴇʜ
– Pᴇʜ, Commented Jul 6, 2022 at 12:30

Foxfire And Burns And Burns · Accepted Answer · 2022-07-06 12:44:55Z

1

If your Pivot Tables names don't follow a pattern, you can create an Array unidimensional and use it to call them. Something like this should be helpful

Sub test()
Dim MyPivotTables As Variant
Dim i As Long

MyPivotTables = Array("", "PvtTable 1", "Pvttable 2", "Pvttable3")

For i = 1 To UBound(MyPivotTables) Step 1 'we omit index 0, so i= 1,2,3 ---> first table, second table and so on
    MsgBox "Index: " & i & vbCrLf & "Pivot Table: " & MyPivotTables(i)
Next i

Erase MyPivotTables

End Sub

answered Jul 6, 2022 at 12:44

Foxfire And Burns And Burns

12.1k2 gold badges19 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ali Over a year ago

Thanks so much, however the names in my question were to make understand that the pivottables don't show in order, in my folder, i don't have names with an incrementing variable, but rather only string names that may change, and so that's why i'm struggling with the order... but thanks for your help !!

Foxfire And Burns And Burns Over a year ago

@Ali That's the point of my code. You don't need names with an incrementing variable, you just need to type them in the line MyPivotTables = Array("", "PvtTable 1", "Pvttable 2", "Pvttable3") in the order you want. You just need the string names

Ali Over a year ago

Thanks a lot !! I have put my pvttable names in an array however then to call each pvttable i use an iterator "i". The problem is that apparently pivottables are called by the order of creation so I can't use the i to name the pvttable ... unless i enter the names in the array in the specific order of which the pvttables were created, but then i run the risk of someone deleting and redoing a pvttable and that order will thus be changed. I am sorry I hope it is understandable. In any case your help is ever so appreciated ! THanks !!!

Collectives™ on Stack Overflow

Change the order of Pivot tables in same worksheet

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related