I need to add a new sheet to a file every week; that new
sheet name becomes Wk 39, the previous sheet was Wk 38,
for all 52 weeks. Is there a way to automatically label
the new sheet to the new week number? I've been trying to
come up with code to do this for awhile, but I'll admit
defeat at this point. Thanks!

Try this

Worksheets.Add.Name = "Wk " & weeknum(Now)

You must check the Analysis toolpack VBA add-in
ToolsAdd-ins in Excel

And in the VBA editor
Tools References, and put a check next to "ATPVBAEN.xls" workbook in the
references list.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Eva Shanley" wrote in message ...
I need to add a new sheet to a file every week; that new
sheet name becomes Wk 39, the previous sheet was Wk 38,
for all 52 weeks. Is there a way to automatically label
the new sheet to the new week number? I've been trying to
come up with code to do this for awhile, but I'll admit
defeat at this point. Thanks!

Ron, thanks for the answer! The only problem is I didn't
think to mention our Wk 1 goes by fiscal year instead of
calendar year. My Wk 1 is actually the week starting
Sat., February 8, 2003. I can modify the code to subtract
a set number of days to get the right week, but that
obviously causes problems for the user if they don't run
this within a certain time period.

-----Original Message-----
Try this

Worksheets.Add.Name = "Wk " & weeknum(Now)

You must check the Analysis toolpack VBA add-in
ToolsAdd-ins in Excel

And in the VBA editor
Tools References, and put a check next
to "ATPVBAEN.xls" workbook in the
references list.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Eva Shanley" wrote
in message ...
I need to add a new sheet to a file every week; that new
sheet name becomes Wk 39, the previous sheet was Wk 38,
for all 52 weeks. Is there a way to automatically label
the new sheet to the new week number? I've been trying
to
come up with code to do this for awhile, but I'll admit
defeat at this point. Thanks!


.

Hi Eva

You can also count the sheets and add one or more if you have other sheets in the workbook also

Worksheets.Add.Name = "Wk " & ThisWorkbook.Sheets.Count + 1


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Eva Shanley" wrote in message ...
Ron, thanks for the answer! The only problem is I didn't
think to mention our Wk 1 goes by fiscal year instead of
calendar year. My Wk 1 is actually the week starting
Sat., February 8, 2003. I can modify the code to subtract
a set number of days to get the right week, but that
obviously causes problems for the user if they don't run
this within a certain time period.

-----Original Message-----
Try this

Worksheets.Add.Name = "Wk " & weeknum(Now)

You must check the Analysis toolpack VBA add-in
ToolsAdd-ins in Excel

And in the VBA editor
Tools References, and put a check next
to "ATPVBAEN.xls" workbook in the
references list.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Eva Shanley" wrote
in message ...
I need to add a new sheet to a file every week; that new
sheet name becomes Wk 39, the previous sheet was Wk 38,
for all 52 weeks. Is there a way to automatically label
the new sheet to the new week number? I've been trying
to
come up with code to do this for awhile, but I'll admit
defeat at this point. Thanks!


.

Ron, I can't thank you enough because I'll use this in a
lot of spreadsheets; it's exactly what I needed. I tried
to come up with this myself before I asked here, but it
always seems like I'm "close but no cigar". But, each time
someone is nice enough to help me out I learn a little
more. Thanks again.

-----Original Message-----
Hi Eva

You can also count the sheets and add one or more if you
have other sheets in the workbook also

Worksheets.Add.Name = "Wk " & ThisWorkbook.Sheets.Count +
1


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Eva Shanley" wrote
in message ...
Ron, thanks for the answer! The only problem is I
didn't
think to mention our Wk 1 goes by fiscal year instead of
calendar year. My Wk 1 is actually the week starting
Sat., February 8, 2003. I can modify the code to
subtract
a set number of days to get the right week, but that
obviously causes problems for the user if they don't run
this within a certain time period.

-----Original Message-----
Try this

Worksheets.Add.Name = "Wk " & weeknum(Now)

You must check the Analysis toolpack VBA add-in
ToolsAdd-ins in Excel

And in the VBA editor
Tools References, and put a check next
to "ATPVBAEN.xls" workbook in the
references list.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Eva Shanley"
wrote
in message news:024801c39f25$a573c560
...
I need to add a new sheet to a file every week; that
new
sheet name becomes Wk 39, the previous sheet was Wk
38,
for all 52 weeks. Is there a way to automatically
label
the new sheet to the new week number? I've been
trying
to
come up with code to do this for awhile, but I'll
admit
defeat at this point. Thanks!


.



.