I create over 350 named ranges through vba code. Every year I need to change them all. Actually I keep the same names and change the cell(s) they refer to.
Using vba, is there a way to "replace" the reference cells or to delete them entirely?
Printable View
I create over 350 named ranges through vba code. Every year I need to change them all. Actually I keep the same names and change the cell(s) they refer to.
Using vba, is there a way to "replace" the reference cells or to delete them entirely?
Delete them and recreate them with VBA. It's not that difficult. You can record a macro and view the code needed. Only a couple lines..
But, 350+ named ranges!!!! OMG!!! That's insane!! We need to work on lowering that number. Talk about a bloated workbook! Is there anyway you can trim that???
No need to delete them,. just re-create, they over-write.Quote:
Originally Posted by firefytr
Guess I was warming up for some deleting of all those names. :devil:Quote:
Originally Posted by xld
Actually, I need that many names. bottom line, it is a calendar. On a worksheet, I have 2 col.
Date
Item.
I have a macro that picks up the dates, creates the "name" (Jan_22), selects the worksheet and then enters the Item text into the cell. Originally, there are over 4,000 lines in the list. If any of the items/dates change, it is a lot easier to make the change in the list than go to the month-sheet and edit the cell.
Getting the list is another story. it worked great last year and now I am modifying it to work with any year regardless of what day of the week Jan 1 falls on.
By the way, I plan on submitting it to the KB.
You may want to have a look at this kb entry:
Justin:
Thanks. but this is about 180 degrees from what I am doing. I found what I needed by taking firefytr's advice and recording a macro and looking at the code. I have done that hundreds of time and don't know why I didn't do it here. I'm closing the thread. But first...
We have over 400 officers with each having 10 - 15+ vacation days a year. However, we can only have a set number of them off on any one given date. When each officer submits his/her wish-list for the year, we can use autofill (etc) to enter their name and date into an excel worksheet. Even though we can sort that by date and manually count each day's requests, this vba code
1. creates a worksheet for each month
2. automatically spreads all requests throughout the year to the specific date (ergo need for named ranges)
3. Prints each months calendar for visual analysis.
4. When a conflict exists, the supervisors determines the winner by senority and makes the changes to the original list.
5. run a macro to clear and reset the calendars in a matter of seconds.
Once the list is approved, it is given to the TeleStaff people and they enter it into the master scheduling program.
We have also used this for training sessions and a variety of other applications.
That does not suggest the need for names to me, you have an immnediate index into the data, the day number, so no need for so many names, which is going to hurt.Quote:
Originally Posted by Kicker
I agree with xld, this does not necessarily necesitate Named Ranges. Depending on your setup and how you would like to accomplish this, you could either use worksheet functions (INDEX/MATCH, etc.) or via VBA (.Find, .FindNext, etc.). I generally prefer the .Find method as, when used correctly, it's exceptionally fast.
sorry but I don't see it.
Example:
I have a list of 4,000 names and dates
I go through that list one at a time - ie. Miller, TJ May 18
I can get to the "May" worksheet. But how would I find the 18th?
When it is finished, I have a printed calendar - 7 days across and however many weeks down. May 18th may show 5 different names.
It is difficult for us to say as we do not have the workbook, but as there must be some rule that you apply to set the name, you could use that same rule directly to get the data for May 18th and output it.
I'l post a copy of the file tonight. Don'thave time to pull a lot of junk out of it now. It will show you how I create the individual "month" worksheets and how I get the calendars.
Ok.
Ctrl-M will get you a custom menu.
CalDates worksheet contains 2 columns of interest. Name and TODate. I have marked the Name with the string of the associated date cell. To test the worksheet,
1. Ctrl-M to show the menu
2. Select Mark Calendar with names. Each "Name" will be placed in the appropriate cell for Month and date.
3. Select "Clear all names from calendar" to clear them
4. Run the macro "CreateCalendar" to create a different year. It will clear everything and ask for the "year" Use the 4 digits year (i.e., 2006)
Ignore any misc test junk that might be floating around.
Please let me know any ideas you have.