[RESOLVED] [Excel VBA] Refreshing multiple pivot tables

I am working on speeding up a program, as usual. It currently loops through every pivot table in the entire workbook and refreshes it, and there are 46 tables. I'm hoping I can just refresh them all at once, especially since I think all of them are using the same source data, which is a table (not a table object, just a bunch of data grouped together, but I can make it a table object, obviously, if necessary) in the Worksheet("Data"). The pivot tables were not created using the VBA code. The program runs, updates the data, then I want to refresh the pivots. The current code is basically:

Code:

---

For each wksht in ThisWorkbook.Worksheets
    For each pvt in wksht.PivotTables
        pvt.RefreshTable
    Next pvt
Next wksht

---

UPDATED: See below, I still need help, have not found solution

As I mention below, I know one option is

Code:

---

ThisWorkbook.RefreshAll

---

HOWEVER, that doesn't work in the program I am dealing with. When I try to use this simpler, and I thought faster, code, I get an error:

Run-time error '1004':

The Microsoft Jet database engine cannot find the input table or query 'whatitscalled'. Make sure it exists and that its name is spelled correctly.

My good. I was looking around and saw that you can do refresh all pivots by clicking in the right place in the toolbar, so I recorded a macro and now I know that I can use:

Code:

---

ThisWorkbook.RefreshAll

---

Let's say I did create these in VBA, though, and I used the same PivotCache for all of them. Could I just refresh the one PivotCache and that would refresh all tables?

My above problem is somewhat resolved. ThisWorkbook.RefreshAll no longer calls errors. That was caused because there was a Connection in the workbook that didn't work properly, i.e., there was no Access table where it was looking for one. And, I know that if I am in a pivot table and click the refreshall button that it seems to refresh all my pivot tables. However, when my program does ThisWorkbook.RefreshAll, many of my pivot tables do not refresh. I am wondering if the problem arises because I just put the data in the source table for these pivots and it hasn't updated enough for the pivots to find them, or something like that? Perhaps putting in a slight pause after putting in the data would be helpful? I don't know so I'm going to ask a new question about this, and mark this as resolved for now.

Okay, this actually is resolved... my problem was calculation was off and when I turned it back to Automatic before refreshing the pivot tables, it worked properly.