Create a macro to copy worksheet 52 times & rename

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello
I have a timesheet we use in our company and its easier to have one worksheet tab for each week of the year.
Is there a way VBA can copy our mastertimesheet tab 52 times (creating 52 more worksheet tabs) and renaming each tab to the week: ie: sheet 1 would be named 01-01-04, Sheet 2 would be named 01-08-04 and so on thru December 2004?
I tried an array macro but got hung up.
Thanks
he4giv/dick
 
Hi

Sure. Try this:

Sub MakeYear()
Dim D As Date, L As Long
For L = 1 To 52
D = DateSerial(2004, 1, 1 + (L - 1) * 7)
Worksheets(1).Copy _
After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(D, "m-d-yy")
Next
End Sub

--
HTH. Best wishes Harald
Followup to newsgroup only please.

he4giv said:
Hello
I have a timesheet we use in our company and its easier to have one worksheet tab for each week of the year.
Is there a way VBA can copy our mastertimesheet tab 52 times (creating 52 more worksheet
Click to expand...
tabs) and renaming each tab to the week: ie: sheet 1 would be named 01-01-04, Sheet 2
would be named 01-08-04 and so on thru December 2004?
 
Back
Top