Rename first X number of worksheets predefined names
 

I have a template that populates based on the last 13 weeks of reports. I
have a worksheet for each week's report. The worksheets are named wk1, wk2,
wk3.....wk13. On a weekly basis, I plan to insert the latest report into a
new worksheet after wk13. Then I will delete wk1. At that point I will need
to rename wk2 to wk1, wk3 to wk2, etc. I would then end up with the latest
13 weeks again. The formula's in my template have references to the sheet
names, so that is why I'll need to rename them. So my question is this:

Is there some vba code that will rename the first 13 worksheets to
predifined names, but stop renameing worksheets once it's finished with the
13th? In my case, rename the first worksheet to "wk1", the second worksheet
to "wk2", etc. It would be great if I could do this with the push of a
button, instead of taking time to rename 13 worksheets each week.

Jeff

Rename first X number of worksheets predefined names
 

Sub WorksheetLoop()
Dim wsName As String
This may get you started in the right direction
Test in a copy of you workbook.
Dim WS_Count As Integer
Dim I As Integer
WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
For I = 1 To WS_Count
wsName = "wk" & I
On Error Resume Next
ActiveWorkbook.Worksheets(I).Name = wsName
Next I
End Sub

"goofy11" wrote:

I have a template that populates based on the last 13 weeks of reports. I
have a worksheet for each week's report. The worksheets are named wk1, wk2,
wk3.....wk13. On a weekly basis, I plan to insert the latest report into a
new worksheet after wk13. Then I will delete wk1. At that point I will need
to rename wk2 to wk1, wk3 to wk2, etc. I would then end up with the latest
13 weeks again. The formula's in my template have references to the sheet
names, so that is why I'll need to rename them. So my question is this:

Is there some vba code that will rename the first 13 worksheets to
predifined names, but stop renameing worksheets once it's finished with the
13th? In my case, rename the first worksheet to "wk1", the second worksheet
to "wk2", etc. It would be great if I could do this with the push of a
button, instead of taking time to rename 13 worksheets each week.

Jeff