Results 1 to 1 of 1
2001-08-28, 14:26 #1
- Join Date
- Jan 2001
- Baltimore, MD, Maryland, USA
- Thanked 0 Times in 0 Posts
Change Range Names wo Asking User (OFFICE97 SR2)
I changed this Post on 8/27/01 10:00pm.
This application has Pivot Table with range defined dynamically to pick up all data returned from an ACCESS database by a MSQuery. The fields in the PT are year-Month (formed by concatenating 2 fields from the Query Table). In the database, the month and year are combined with some useless text, making the concatenation necessary. The rows in the PT are variables (records from the ACCESS database). The cell values, then, are QC readings for each variable for each yyyy-mm. I use the variable names as Range Names. Each Variable Named Range us a row in the PT (which includes the values for all the yyyy-mm returned by the query for that variable. Initially, I used the INSERT, NAME, CREATE command to create the ranges. The ranges are also used in a pick list that allows the user to select the variable for which he/she wants to see the trend of QC readings over the months for which we have readings.
The user will want to refresh the query and the PT once a month, when (they believe that) data for a new month has been added to the database. This means that the code has to re-create the range-names so that they all include the data from the additional month (the last column in the PT). I inserted the code to do this but it requires the user to click 'Yes' to the message box which asks whether or not to replace the existing range definition for each variable. There are 150 to 250 variables, so this can get