Renaming tabs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got Tabs like week1 week 2 week3 week
Next week I'll have to rename week4 to week5 and week3 to week 4 and so on, then insert a new and name it week1
Any ideas
Thanks all
DJ
 
Why not give the tabs a name relating to the date?

E.g. for this week, ending on Friday 13 February 2004, you could name the
tab 20040213, or something similar such as 2004~02~13.

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



Duncan J said:
I've got Tabs like week1 week 2 week3 week4
Next week I'll have to rename week4 to week5 and week3 to week 4 and so
Click to expand...
on, then insert a new and name it week1.
 
Duncan,

Try something like the following:

Dim N As Long
With ThisWorkbook.Worksheets
For N = .Count To 1 Step -1
.Item(N).Name = "Week" & Format(N + 1)
Next N
.Add(before:=.Item(1)).Name = "Week1"
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


Duncan J said:
I've got Tabs like week1 week 2 week3 week4
Next week I'll have to rename week4 to week5 and week3 to week
Click to expand...
4 and so on, then insert a new and name it week1.
 
Duncan J said:
I've got Tabs like week1 week 2 week3 week4
Next week I'll have to rename week4 to week5 and week3 to week 4 and so
Click to expand...
on, then insert a new and name it week1.
Any ideas?
Thanks all,
DJ
Click to expand...

Hi !

May not be the most elegant solution, but it works - regardless of how many
digits the number has got. All that has to be ensured is that the number
part is always at the end of the sheet name :

'**********************************
Sub test()

Dim p%, sSub$, sNum$, sTxt$

For Each sh In Sheets

p = Len(sh.Name) + 1

Do
p = p - 1
sSub = Mid(sh.Name, p, 1)
Loop While IsNumeric(sSub)

sNum = Mid(sh.Name, p + 1, Len(sh.Name) - p)
sTxt = Left(sh.Name, p)

sh.Name = sTxt & CStr(CInt(sNum) + 1)

Next sh

End Sub
 
Thanks for the ideas guys. The only reason I can't date it because the vlookups are in th macro. So I need it to be generic
Thanks again!
 
Back
Top