VBA help for inserting/naming worksheets

Posted by Ghentry on January 07, 2002 11:42 AM

I am trying to figure out how to write a macro that will copy sheet 1 which is named "01-02", the first Wednesday of the year, and rename it "01-09", the second Wed, and so on. Each time I run the macro, I need a new sheet created with the next Wednesday of the year. Also, I would like to make the same change to cell a4 on each worksheet. Any help would be appreciated.

Posted by Gary Bailey on January 07, 2002 12:28 PM

Try the following code

Dim dtmLastWed As Date, dtmNextWed As Date

dtmNextWed = DateAdd("d", 7 - DatePart("w", Date, vbThursday), Date)
dtmLastWed = DateAdd("d", -7, dtmNextWed)

Sheets(Format(dtmLastWed, "mm-dd")).Copy ActiveSheet
ActiveSheet.Name = Format(dtmNextWed, "mm-dd")

Range("a4").Value = "your change here"

Careful of the text wrapping.

Gary