PDA

View Full Version : VBA for Pivot Tables Help (Dates)



Eric58132
06-10-2009, 11:47 AM
Good afternoon everyone,

Ok here's the scoop, I've been asked to try and automate a reporting file that is updated weekly for my company. It currently contains 75 pivot tables, all of which are linked a single table in an access DB.

There are 15 or so date fields in the access table which are used ultimately placed in the pivot rows of these pivot tables (a random 1 of the 15 in each table, so some overlap). The results of these pivot tables feed into a "master sheet" where the week's new results are populated automatically.

Currently, the updator of the file refreshes the pivot tables, unselects the dates from two weeks ago in the pivot row of all 75 pivot tables, and then chooses the dates for last week's data.

Now, I'm trying to think of how I can automate this and came up with two potential (hypothetical) ways. One is to create a "Week value" associated with every date entry in every date field in the access database side, and from there.... create a master pivot table with where all of those "week columns" are placed in the page fields of a pivot table..... and then use vba to make any changes to this pivot table update all other pivot tables.

Another way I was thinking was to have two cells somewhere in the excel file (which could be changed), where the first and last dates of the week were entered and somehow these two dates became criteria for filtering the row fields of each table.

Thoughts? Anyone know if these options are possible? I'm not 100% fluent in VBA but I have somewhat of an idea on what I'm doing so any assistance would be most appreciated.

mdmackillop
06-10-2009, 12:26 PM
Could you make up a small sample indicative of your actual setup?