Rename Copied Tab Based On Ascending Weekly Sequence

[FrenchCelt]

Hello,

I have a macro that copies the current sheet and then clears out old data so I can update it, but I can't figure out how code it to rename the tab using the weekly sequence that I use (the naming convention is Wk 1, Wk 2, Wk 3, etc.). So when Wk 33 is copied, I would manually rename it to Wk 34, but I don't know how to code that. Can anyone help?

 

[RobP]

Hi,

to rename your sheet, you could use something like this: ?

Rgds
Rob

Sub renamesheet()


Dim Ws As Worksheet
 
    this_week = Application.WorksheetFunction.WeekNum(Now()) - 1 'saves it as last weeks week no.
    
    Set Ws = Worksheets("Sheet1")

    Ws.Name = "wk" & this_week


End Sub

 

[FrenchCelt]

Run-time error '9':
Subscript out of range on this part:

Set Ws = Worksheets("Sheet1")

 

[RobP]

maybe you don't have a "sheet1" in your workbook ?

this would normally be the sheet you want to rename to something else ?

 

[FrenchCelt]

No, all the tabs are named for the various weeks of the year. When I copy the current sheet, it's renamed to the previous week (2), so Wk 33 (2) in this case.

 

[RobP]

so whats the name of the tab that you want to rename as week 34 ?

 

[FrenchCelt]

I edited my last reply to include this info.

 

[RobP]

ok, thanks, sorry, I missed that point. So try this instead: It works based on the current sheet you work on.

Sub renamesheet()


Dim Ws As Worksheet
 
    this_week = Application.WorksheetFunction.WeekNum(Now())
    
    Set Ws = ActiveSheet 'Worksheets("Sheet1")

    Ws.Name = "wk " & this_week


End Sub

 

[FrenchCelt]

I actually worked out the logic on my own using your initial code as a launching point. This is what I came up with and just tested it. Works fine and in the format I need:

ActiveSheet.Name = "New Week"
    Dim Ws As Worksheet
 
    this_week = Application.WorksheetFunction.WeekNum(Now()) - 1 'saves it as last weeks week no.
    
    Set Ws = Worksheets("New Week")

    Ws.Name = "Wk " & this_week

I had to tinker a little to get Wk 34 instead of wk34, which is what your code did. Thanks, you did the hard work.

 

[RobP]

yep, I realised I hadnt added the space, so corrected it with my last post hoping you hadn't noticed

thanks for feedback - glad to have been of use..

Rob