Is there a simple way to fill the days of the current month, e.g.
November 1 to 30 and February 1 to 28(or 29)?
I want to create a template to record temperature, rainfall etc for
each day of the month. Ideally this would create a new worksheet,
even better if it named that sheet November 2011 etc.
Thanks in advance
Jim's is probably the "simplest" way.
Or you could automate the entire process of adding and formatting each new month sheet using VBA and an Auto-Open procedure (runs whenever the workbook opens). This is more difficult to setup, but, if well thought out initially, can save a lot of time (as well as ensure standardization) down the road.
This procedure would
Check to see if there is a month sheet representing the current month
If there is, just exit.
If not
Add a new worksheet after the last one
Name the worksheet with the current Month and Year
Add Labels across the top
Add the days of the month down the first column
Format the cells appropriately.
This kind of automation is handy if you have complicated formatting you want to apply to the worksheet
To set up something like this, create a new workbook with at least one worksheet; maybe it will be your Summary sheet.
Then save it (as macro-enabled workbook if you have a later version of Excel).
Then add the code below.
To enter this event-triggered Macro, right click on the sheet tab.
Select "View Code" from the right-click drop-down menu.
In the left hand window, you should see the Project Explorer with a list of projects, one of them should be titled VBAProject(your_workbook_name). Expand that so as to see an Object titled "ThisWorkbook". Double Click on ThisWorkbook.
Then paste the code below into the window that opens.
Examine the code so you can see how the labels are created and the data entry stuff is formatted, so you can adjust it to your specific requirements.
Close and save the workbook. When you reopen it, it should create and format a sheet for November.
==================================
Option Explicit
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim wsName As String
Dim i As Long
Const FirstRow As Long = 2 'This is the first row of Data, NOT lables
Dim vLabelArray As Variant
wsName = Format(Date, "mmmm yyyy")
On Error GoTo NewWorksheet
ThisWorkbook.Worksheets(wsName).Activate
Exit Sub
NewWorksheet: If Err.Number = 9 Then
On Error GoTo 0
Set ws = ThisWorkbook.Worksheets.Add(after:=Worksheets(Worksheets.Count))
ws.Name = wsName
vLabelArray = Array("Date", "Temperature", "Humidity", "Rain", "WindSpeed")
For i = 1 To Day(DateSerial(Year(Date), Month(Date) + 1, 0))
Cells(FirstRow - 1 + i, "A").Value = DateSerial(Year(Date), Month(Date), i)
Next i
Range(Cells(FirstRow, "A"), Cells(FirstRow - 2 + i, "A")).NumberFormat = "mmm d, yyyy"
Range(Cells(FirstRow, "B"), Cells(FirstRow - 2 + i, "B")).NumberFormat = "0.0 " & Chr(176) & "C"
Range(Cells(FirstRow, "C"), Cells(FirstRow - 2 + i, "C")).NumberFormat = "0%"
Range(Cells(FirstRow, "D"), Cells(FirstRow - 2 + i, "D")).NumberFormat = "0.0\"""
Range(Cells(FirstRow, "E"), Cells(FirstRow - 2 + i, "E")).NumberFormat = "0.0 " & """M/s"""
With Range(Cells(FirstRow - 1, "A"), Cells(FirstRow - 1, UBound(vLabelArray) + 1))
.Value = vLabelArray
.EntireColumn.AutoFit
End With
Else
MsgBox ("Error: " & Error(Err.Number))
Stop
End If
End Sub
================================
