Changing Data Source in Excel on Pivot Tables

[patriciaanne]

Hi- I have a pivot table that is linked to another Excel spreadsheet in a separate file and the name of that file changes every month (only 1 sheet in the file). I have gone into Workbook Connections and removed the old workbook connection and added the new one and clicked Refresh and it gives me a warning and turns the pivot table into a static data range and the report filter drop down no longer works. So, I tried adding the new file name in first and then removing the old file and same problem. Any ideas?



To everything there is a season, a time for every purpose under the sun.

 

[Gavona]

If you are looking for a code solution then you should post to forum707

Manually right-click on the pivot table then select PivotTable Wizard, back and enter/modify the source location.

I suggest that you use named ranges.

How do you get to Workbook Connections - via code?

Gavin

 

[SkipVought]

hi,

Select in the PivotTable.

Hit the BACK key to get to the Source data. If it does not refer to External Data, you will have to start over.

Otherwise, modify the source selection in the External Data button.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, I'd CHANGE the name of the source workbook each month to be a standard name. Then you won't have to mess with the wizard.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

Agree with Skip about a standard filename. But I would select Excel List or Database in Step 1 of the PivotTable wizard.
So in step 2 of 3 the range would be something like:
tSourceFileCurrentMonth.xls!Alldata
rather than
tSourceFileMonth2.xls!Alldata
which would need amending each month.

In tSourceFilexxxxx.xls you would need to first define the named range "Alldata"
In code, assuming the table starts in A1 and has no totally blank rows or columns, something like
Range("A1").CurrentRegion.name="Alldata"
Without code select A1
Edit,GoTo,Special, CurrentRegion
Then type Alldata in the name box




Gavin

 

[patriciaanne]

Thanks for the suggestions and I think the easiest is to try to keep the same name since I am a novice at VBA.
This is Excel 2007- I don't think there is a pivot wizard anymore. This version lets you add as many new source files as you want but apparently it assumes that you just select the one you want to link to because even if I add a new one- connect to it and then remove the old source, I lose the filter buttons. Ah the wonders of Excel or should I say Microsoft.

Thanks again.



To everything there is a season, a time for every purpose under the sun.