Add defalt sheet to macro

Microsoft Excel 2003 (full product)
May 12, 2010 at 22:07:07
Specs: Windows XP
I have found this macro that works good and generates a sheet with each day of the month.But all the sheets are blank that are generated how do I use a basic template to be plugged in and not a blank sheet.

here is the maco

Sub DoDays()
Dim J As Integer
Dim K As Integer
Dim sDay As String
Dim sTemp As String
Dim iTarget As Integer
Dim dBasis As Date

iTarget = 13
While (iTarget < 1) Or (iTarget > 12)
iTarget = Val(InputBox("Numeric month?"))
If iTarget = 0 Then Exit Sub

Application.ScreenUpdating = False
sTemp = Str(iTarget) & "/1/" & Year(Now())
dBasis = CDate(sTemp)

For J = 1 To 31
sDay = Format((dBasis + J - 1), "dddd mm-dd-yyyy")
If Month(dBasis + J - 1) = iTarget Then

If J <= Sheets.Count Then
If Left(Sheets(J).Name, 5) = "Sheet" Then
Sheets(J).Name = sDay
Sheets.Add.Move after:=Sheets(Sheets.Count)
ActiveSheet.Name = sDay
End If
Sheets.Add.Move after:=Sheets(Sheets.Count)
ActiveSheet.Name = sDay
End If
End If
Next J

For J = 1 To (Sheets.Count - 1)
For K = J + 1 To Sheets.Count
If Right(Sheets(J).Name, 10) > _
Right(Sheets(K).Name, 10) Then
Sheets(K).Move Before:=Sheets(J)
End If
Next K
Next J

Application.ScreenUpdating = True
End Sub

thanks for the help

See More: Add defalt sheet to macro

May 13, 2010 at 04:08:46

If you have a worksheet named "Template" then you can copy it and use it as new worksheets:

Worksheets("Template").Copy after:=Sheets(Sheets.Count)

You only need the one line to 'copy' and 'add' it. Use the same after command to position it as required.

Hope this helps


Report •

May 13, 2010 at 18:41:46
Thanks for the quick reply.

I am having trouble positioning that line of code right. I can get it to take the place of the first day but it does not make the whole month the same. Where should I place the line of code in the macro?

This works with a sheet in the excel document that is named "template" correct?

Report •

May 14, 2010 at 09:30:19

replace both lines:
Sheets.Add.Move after:=Sheets(Sheets.Count)
Worksheets("Template").Copy after:=Sheets(Sheets.Count)

I didn't try and work out what your code did - I just replaced those lines, so that the worksheet named "Template" would be copied, and inserted, rather than having a new worksheet added.

The workbook containing your macro must contain a worksheet name "Template"


Report •
Related Solutions

Ask Question