Two Quick Questions: Using Count Method + Using Offset Property

[JDHendrix]

Hello all, I have a couple of quick questions.

This is my code...I need to remove the hard-coded reference to A31:F32 in Step 1.4 and use A2 as an anchor and Offset property. This is because months have different numbers of days.

Also as of now the new worksheet for the new month...say March does not go to the end, it sits before February which doesn't make sense. I need to use count method somewhere in step 1.2 to fix that issue.

Thanks for any help!

[VBA]Sub CoolMacro()
'CoolMacro Macro
Dim dateStart As Date
Dim dateStop As Date
Dim indexMonth As Integer
Dim numDaysInMonth As Integer
Dim nameMonth As String
Dim countSheets As Integer
'1.1 Ask user for starting date (Assumption is that this will be the 1st of the month.)
'Ask user for starting date (Assumption is that this will be the 1st of the month.)
'Determine needed information from starting date
dateStart = InputBox(prompt:="Please give the starting date of the month in the form mm/dd/yyyy")
dateStop = WorksheetFunction.EoMonth(dateStart, 0)
indexMonth = Month(dateStart)
nameMonth = MonthName(indexMonth)
numDaysInMonth = dateStop - dateStart + 1
'1.2 Copy Template sheet from the macro workbook to the end of the data workbook
ThisWorkbook.Sheets(1).Copy After:=Sheets(1)
'1.3 Fill in dates for the desired month
Range("A2").Select
ActiveCell.FormulaR1C1 = dateStart
Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
xlDay, Step:=1, Stop:=dateStop, Trend:=False
'1.4 Clear the 3 rows following the last date of the Month
Range("A31:F32").Clear
'1.5 Rename new sheet using the desired month
Sheets("Template").Name = nameMonth
ActiveWindow.ScrollRow = 1
'1.6 Select cell B2 on the new sheet
Range("B2").Select
End Sub[/VBA]

[mikerickson]

As written, the tabs will be in this order.

StartTab, December, November, October, ...April, March, February, January.

If you want them to be

StartTab, January, February, March, ...
[vba]With ThisWorkbook
.Sheets(1).Copy After:=Sheets(.Sheets.Count)
End With[/vba]

As for 1.4, something like this should work:[vba]Range("A2").Resize(monthLength, 1).ClearContents[/vbA]