Copy Worksheet and rename

I need to copy my active worksheet and have it duplicate it for each saturday of every week for the entire year of 2014. I need the format of the worksheet to be named: "01-04-14". How can I write a macro to have it duplicate my sheet with 52 sheets with each saturday as the name?
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
Here is a macro that will copy the active worksheet once for every Saturday of the year. It will put these sheets in order after the last tab, and name them as mm-dd-yy.
Sub SaturdaySheetMaker()
Dim wsTemplate As Worksheet
Dim dat As Date, dat2 As Date
Dim celHome As Range
Application.ScreenUpdating = False
Set wsTemplate = ActiveSheet
Set celHome = ActiveCell
dat = DateSerial(Year(Date), 1, 1)
dat2 = dat + 6 - Weekday(dat, vbMonday)
If dat2 < dat Then dat2 = dat2 + 7
Do Until Year(dat2) > Year(dat)
    wsTemplate.Copy after:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
    ActiveSheet.Name = Format(dat2, "mm-dd-yy")
    dat2 = dat2 + 7
Loop
Application.Goto celHome
End Sub

Open in new window

0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you very much!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.