I want to make a possibly simple modification to the attached Excel 2003 Workbook, a version of which I use in administering the large lottery pool at http://www.ussrankin.org/lottery. I had to strip quite a bit out of the attached version, since the full version is far too large to upload here.
I’m confident that I could figure out how to do the modification, but my higher Excel skills are rusty, and there would be a lot of tears involved. I’m hoping that an expert Lounger can do it with his eyes closed.
Look at sheet wk1 of the workbook. Button 985 at the top left runs Macro1, which puts a copy of the NewWk sheet into the leftmost position at the bottom of the workbook. I click this button when I need to create a new sheet, which happens late every Friday night.
Then I manually rename the new sheet ‘wkN’ where N is one more than the integer in cell L1 of the sheet on which Button 985 is pushed. The highest possible N is 14 (wk15 would never be reached).
I also manually put N into cell L1 of the new sheet. This updates a lot of fields on the new sheet, and gets it ready to accept data for the new week.
I’d like to automate those two manual tasks, so that clicking Button 985 not only creates the new sheet, but also renames it and puts the proper number into cell L1.
That is the main part of my need. Now for a refinement:
I’d also like Button 985 to be visible or active only during the time when I might need it. That time begins any time I update the sheet after 11:00 PM on the Friday shown on the sheet, and ends when I create the new sheet.
So what should happen is this:
When the leftmost worksheet is updated after 11:00 PM on Friday, Button 985 becomes visible and active.
When Button 985 is clicked, it should become invisible/inactive. Clicking it will create and name a new worksheet, and the new week number should be placed into cell L1. Button 985 on the new sheet should be invisible/inactive until the new sheet is updated any time after 11:00 PM Friday.
It’s not really necessary, but probably Button 985 on the NewWk sheet should always be visible, so as not to confuse those who look at NewWk.
This is all very slick, and I have a feeling it’s easy to do for somebody with the proper Excel skills. If you’d like to help, have at it!
PS – FYI, it’s just a matter of time before this application migrates to Excel 2010, but I’m in no hurry to do that. If there’s some major benefit to doing it now, please let me know.