VBA code for copying a Worksheet from one week to another

[accountant_101]

Hi there,

Having an issue copying over sheets and converting one week to another in excel using Macros.
Currently the data is set up in formulas dragging from multiple sources and this report is the final product.
What I want to do is copy the worksheet to another worksheet and add on a week for the following week in the worksheet tab name.

Current format for example is "Oct17 w 1". I want to convert the next sheet name to "Oct17 w 2"... and so on for when I use the macro again for next week "Oct17 w 3 etc etc.

Thank you in advance

 

[My Aswer Is This]

You said:
What I want to do is copy the worksheet to another worksheet and add on a week for the following week in the worksheet tab name.

So are you saying copy to a existing worksheet or make a new copy of this worksheet and give this worksheet a new name. And are you saying the new worksheet name will always be:

"Oct17" & then w 2 and next time "Oct17" and w3. What happens when this month is not October.

 

[accountant_101]

Yes I want to copy the current worksheet to a new worksheet, keep all the formulas etc. in it, but only change the Tab name to the current week I am reporting on.

Im fairly new to macros so im not sure if id put a new macro in for a new month or add it in to the current macro (Wouldn't know how to do either lol)?

 

[My Aswer Is This]

Try this:
Will Make a new copy of the active sheet and name the new sheet current week

So if you run the script today (10-26-17) your new sheet will be name "43"

Sub Copy_Sheet()
On Error GoTo M
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(Now, "ww")
Exit Sub
M:
MsgBox "That sheet name already exist"
End Sub

]

 

[My Aswer Is This]

Or try this if you want the new sheet name to look like this "Week 42"

Sub Copy_Sheet()
On Error GoTo M
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Week " & Format(Now, "ww")
Exit Sub
M:
MsgBox "That sheet name already exist"
End Sub