View Single Post
  #1   Report Post  
Rachel Gonsior
 
Posts: n/a
Default Pivot Table Problems

I created a workbook (in Excel 97) that has 8 identical sheets in it and
another sheet with a pivot table to summarize the data on the 8 sheets. I
added a button to run a macro to refresh the pivot table after the data was
entered on the 8 sheets. I sent the file to several people to be completed
monthly. The file needs to be saved with a new name each month to identify
the report period. The users have various versions of Excel. When I opened
the returned files, some were OK but for some, Excel reported that the pivot
table was invalid and had been discarded. I recreated the pivot tables in
Excel 2003 but still encounter the same problem. The problem doesn't seem
to be related to the Excel version, however, because I'm having the same
problem with files used only on machines with Excel 2003. I'm suspicious
that it might be related to the changing file names and/or paths. I've had
to recreate the pivot tables in these files repeatedly so I decided to try
to automate the process. I recorded a macro while creating the pivot table.
But I'm having 2 problems.



When I look at the recorded code, it has the file name in it, as follows:

CreatePivotTable TableDestination:="'[DAV 02-28-05.xls]Recap'!R3C1",
_

TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10



Is there any way to change the code so it doesn't have to be manually edited
to change the file name every time it's run? The users also need to be able
to refresh the pivot table to see the summary data so I need to make the
macro work without editing.



The second problem is that if the existing pivot table IS valid, the macro
bombs because the pivot table already exists. Ideally my macro would just
refresh the table if it still exists or recreate the table if does not exist
but I haven't been able to figure out how to do that.



I would appreciate any help. And I would like to thank all of you who have
contributed so much of your time and knowledge to helping others use Excel.
I have learned so much from reading this newsgroup! Thank you!

Becky