use existing pivot table with new data

[Laavista]

I'm using Excel 2007.

I have a spreadsheet which will be updated each month with just that month's data.

Sheet1: data
Sheet2: pivot tables
(and lots of pivot charts)

The user will copy in the new month's data into Sheet1 (this data replaces the old data)
I need to use the pivot tables already created in Sheet2.

The user runs a macro to do various things, and the pivot tables needs to be updated so the pivot charts reflect that month's data.

Problem:
After the new month's data is copied in and I refresh the pivot tables in the vba module, not all of the columns are in the "data source" and the pivot tables I originally created are not available since all the fields are not listed.

Any suggestions?

 

[Jerry Sullivan]

Has that field just been renamed in your new data or is it missing?

If it's missing and it's needed for your pivots, you'll need to somehow add get this data (or create it if it can be derived from the new data with a formula).

Could you give an example of a field that you are using in your existing pivot tables that isn't included in your new data source?

 

[Laavista]

Thanks, Jerry, for replying.

It's actually columns that are not being included when the pivot tables are refreshed.

Columns A-C are included, but it doesn't include columns D-H in the data range.

Appreciate your help!

 

[Jerry Sullivan]

Are you saying that before running your macro, you're pivot tables reference a data source in Sheet1 Columns A-H, but after running the macro the pivots only reference Columns A-C?

If that's the case please post your macro so we can see where that change is occurring.
One option to consider is to make your data source a dynamic named range. That way it will resize as the range of your data in Sheet1 changes.

 

[Laavista]

Are you saying that before running your macro, you're pivot tables reference a data source in Sheet1 Columns A-H, but after running the macro the pivots only reference Columns A-C?

If that's the case please post your macro so we can see where that change is occurring.
One option to consider is to make your data source a dynamic named range. That way it will resize as the range of your data in Sheet1 changes.

Jerry--I really like your idea about the dynamic named range. I had not thought of that!!
Thank you so much for taking the time to help me with this!