Excel Pivot Tables 4

[janetlyn]

I am running Excel 2002 on an XP. I have a pivot table that is pulling data from other workbooks. When I open the file it gives me a message it cannot find one of the workbooks as a pivot table source. I have opened all my wizard formulas and cannot find the table it is referring to in the error message. Is there a way to do a "find" of pivot tables to find this errant workbook? Thanks, JL

 

[koresnordic]

Not that I am aware of, but the pivot may be on a hidden sheet. Try checking this. To find any hidden sheets, highlight all the sheets (left click the left most one, move to the right most one and while holding down the shift key, left click the sheet name). Then select the menu options: format > sheet > unhide.



Graham

 

[janetlyn]

That did not work. The option to "unhide" was grayed out and the "hide" option was the only one visible, so I assume that means I did not have any hidden worksheets. Any other thoughts? JL

 

[combo]

I would try to localize pivot tables using the wizard.
Go outside any pivot table and activate wizard (Data > Pivot table...). Point "other pivot table" as a source data (step 1), if you click next, in the step 2 you will see available pivot tables. There are only page and table names, but this can be useful to localize them.

Another way is to use VBA. This small piece of code will display all table localizations in the workbook:
[tt]Sub GetPT()
Dim PTable As PivotTable
Dim wksWorksheet As Worksheet
For Each wksWorksheet In ThisWorkbook.Worksheets
For Each PTable In wksWorksheet.PivotTables
MsgBox wksWorksheet.Name & vbLf & PTable.TableRange1.Address
Next PTable
Next wksWorksheet
End Sub[/tt]

 

[janetlyn]

I'm sorry Combo. I feel like an idiot and not the Excel expert I thought I was. I will admit Pivot tables are my weakness. I tried doing your first suggestion and after I pointed to the other pivot table and hit next, it dropped down a box that said "List". It showed me two items (00-12) which is a project number and one of the fields I highlighted as being a source and (0). I did not see a list of tables. Next I tried the VBA code. Once I figured out how to get to the VBA screen, I highlighted your code and pasted it to the VBA screen. Then I saved. Then I got lost and could not figure out what to do until I was typing this and it hit me. So I went back to the code and ran it. Duh. It brought a box showing me one of my sheet names and a range of cells. I have one sheet for each month of the year and one sheet that add ups all those months. It brought up a box and range of cells four times for each sheet with the sheet name being the same but the range of cells being different. What does it mean "table localizations"? Thanks so much for your help. Janet Lyn

 

[combo]

Janet, may be I was too short. Additionally, if you have many pivot tables in the workbook, a lot of messages created by macro can be confusing.
As for using the code - you did well, the macro takes every pivot table in the workbook and displays, for each table, the sheet name and range where you can find the table. It is to help to see if there are any tables on hidden sheets (sheet can be hidden via code in the way you can't normally unhide it, which can be important if someone else elaborate the worksheet) or far from normally used area.

Back to your original question, you can refresh table by table and see if error is not reported. If so, the table referes to invalid source

 

[janetlyn]

Combo, you are a genius. I went back and ran the code again. While it was going through all those tables, I was thinking what you said about hidden tables. It dawned on me that last year I let an employee go and for the rest of the year, hid his table. When I copied the file to use this year, I forgot to unhide and delete his tables. Thanks so much. If you can help with another problem, see my other thread Excel 2002 Links. No one seems to know the answer or even gives me any suggestions. Thanks, JL