Workbook question...
I need to create a workbook for each month of the year. Each workbook needs
to have a worksheet for each day of the month. I want to be able to create the worksheet for the 1st of the month and be able to copy it out 30 times, once for each day. The header of the worksheet has a date field. I don't want to manually have to update each day of the month, so how can I copy the sheet for day 1 so that the date chages to the 2nd, 3rd, 4th..... 31th as I copy it. I hope the question makes sense... any suggestions are appreciated. |
Workbook question...
Maurice wrote:
I need to create a workbook for each month of the year. Each workbook needs to have a worksheet for each day of the month. I want to be able to create the worksheet for the 1st of the month and be able to copy it out 30 times, once for each day. The header of the worksheet has a date field. I don't want to manually have to update each day of the month, so how can I copy the sheet for day 1 so that the date chages to the 2nd, 3rd, 4th..... 31th as I copy it. I hope the question makes sense... any suggestions are appreciated. I question why you want to disperse your data across 365 worksheets in a year. This will make a nightmare of summarizing your data. But I suppose you have your reasons. Here is one way to do what you ask using VBA. This assumes the first day of the month is in cell A1. The code will create as many sheets as there are days in the month, name each sheet accordingly, and place the appropriate date in cell A1 of each new sheet. ' begin code ------------------------------------- Sub AllDaysAsWorksheets() Dim d As Date If Sheets.Count 1 Then MsgBox "More than 1 sheet exists. Quitting now." Else Application.ScreenUpdating = False Sheets(1).Activate d = Range("A1").Value Sheets(1).Name = Format(d, "yyyymmdd") d = d + 1 Do Sheets(1).Copy After:=Sheets(Sheets.Count) With Sheets(Sheets.Count) .Activate .Name = Format(d, "yyyymmdd") End With Range("A1").Value = d d = d + 1 Loop Until Day(d) = 1 Application.ScreenUpdating = True End If End Sub ' end code --------------------------------------- If you are not sure how to use this, first call up the VBA editor by pressing Alt+F11, right click in your project and select Insert | Module and paste the code above in the blank editing area that appears. Press Alt+Q or Alt+F11 again to return to the workbook. Now press Alt+F8 to call up the Run Macro dialog, select "AllDaysAsWorksheets" and click Run. There is one little safety feature in that the code will not generate more worksheets if there is already more than one. |
All times are GMT +1. The time now is 04:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com