I have a workbook that consists of 28-31 worksheets, depending on the number of days in a particular month. A template is utilized to provide a new workbook for the upcoming month. The worksheet names are currently edited manually for each day of the month (i.e. '1-1', '1-2', etc. for January). The full date and day of the week are currently entered manually in seperate cells in each worksheet.
What I'm looking for is a VBA macro to automatically detect whether or not the workbook name either contains '*.xlt' or 'template' in the file name (a template file). That would automatically trigger a prompt requesting the following input: "Which month do you want to initialize (1-12)?" Depending on the month entered, the worksheet names would automatically be created (i.e. if '2' is entered, the worksheet names would become '2-1', '2-2, '2-3', etc.) and any additional worksheets beyond the end of the month would be deleted or created as needed (i.e if last month's template had worksheets named 1-1 thru 1-31, and '4' is chosen, worksheet '1-31' would be deleted and if last month's template had worksheets named 4-1 thru 4-30, then May's template would add an additional worksheet for the 31st day).
The last worksheet always is the summary worksheet which totals entries from the other worksheets (utilizing formulas such as sum('1-1:1-31':q6), so any worksheets added or deleted would need to be inserted or deleted prior to that worksheet (which is always named 'Monthly Totals').
Once the worksheet names (and the correct # of worksheets for the # of days of the chosen month) are completed, a cell in each worksheet needs to have the date put into 1 cell (doesn't matter where) and the day of the week in another. I can get day of the week with "=text(a1,"dddd") as long as I manually enter a date such as 1-26 into the other cell with the date. I've been unable to locate any means of accessing the name or title property of a worksheet with an inline formula (which contains the date I want to use).
Thanks for your help,
Danno
Start Free Trial
