Updating data in a linked table
In Settings I had to change Automatic Update command to Full Calculate and then back again.
A pivot table created this way has limited features, and isn't much use in summarizing Excel data.
I usually recommend that you move all the data onto one worksheet, if it will fit, or store it in a table in Access, then use that as the source for the pivot table.
(You might remember Héctor's innovative Filter Pivot Table Source Data example, posted earlier this year.) To adjust their sample code to work in your file, you'd replace the sheet names in the Create Connection code.
To go to the Create Connection code, right-click on the "Create Empty Table" button, and click Assign Macro, then click Edit.
Visit Stack Exchange is pressed or when the cells are clicked out of.
However, it's a bit tedious to set up, especially if you have more than a couple of tables.
This line is further down in the Create Connection code. Then go back to Excel, click the button on the worksheet, and a summary pivot table will be automatically created.That probably turned the cell into a text format cell, even though technically the cell was still a number and listed as one under the formats.I stripped the quotes and the cells calculated as normal. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).I checked all the normal things, like cell type, auto calculation, etc...What I found to be the problem was the formula and the fact that I surrounded numbers with quotes.Somehow PI's Calculate method was no longer running during the normal workbook recalculate.