Copying and naming WS with VBA

[oysterriver]

This sounds so simple that I can’t believe I haven’t been able to do it. I need to create a workbook with the same calendar template copied onto separate worksheets for each week of the year. Each WS needs to be named based on the last date of the week (e.g., 1-7-2012, 1-14-2012, etc.). It seems like something I should be able to do with VBA, but I’m stuck. Any ideas?

 

[kchiba]

Hi,


Try the following.


On Sheet1 in C2 enter the first date that you want.

In D2 enter the following formula '=TEXT(C2,"dd-mmm-yy"), this converts the date to Text

in C3 enter the number of sheets you want to add.


You can the add any other instruction before the Next statement.


Sub AddShts()

ShtCount = Sheets("Sheet1").[C3]


For x = 1 To ShtCount

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Sheets("Sheet1").Range("D2")

Sheets("Sheet1").[C2] = Sheets("Sheet1").[C2] + 7


Next x

End Sub


The above Worksheet.ADD code snippet is from WWW.OZGRID.com