Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rename category names | Charts and Charting in Excel | |||
Check if a predefined number of workbooks exist in a specific directory | Excel Programming | |||
Rename files with variable names | Excel Programming | |||
Rename Defined Names | Excel Worksheet Functions | |||
Cycling through predefined worksheets | Excel Programming |