Macro for changing tabs
 

Duncan,

Here is the solution I posted to your question yesterday.

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

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


"Duncan J" wrote in message
...
I have a vlookup and other formula's in this macro so I cannot
name the tabs by date.
So I have tabs named week1 week2 week3 week4 week5 ect then
after that have other tabs
Top Stockweek1 Topstock week2. ect I need a something that will
change week1 to week 2, week2 to week3 and so on and insert a
blank sheet name week1. So I can paste the new data in and run
the macro's.
I had a few suggestion but so far they haven't worked.
Thanks again!!

Macro for changing tabs
 

Sub AddWeek1()
Dim N As Long
Dim sName As String
Dim sNum As String
With ThisWorkbook.Worksheets
For N = .Count To 1 Step -1
sName = .Item(N).Name
If LCase(Left(sName, 4)) = "week" Then
sNum = Right(sName, Len(sName) - 4)
If sNum = "1" Then itm = N
.Item(N).Name = "Week" & CLng(sNum) + 1
End If
Next N
.Add(befo=.Item(itm)).Name = "Week1"
End With

End Sub

It wasn't clear to me if you wanted similar functionality for the
TopStockWeek sheets. This just works on the Week Sheets.

--
Regards,
Tom Ogilvy




"Duncan J" wrote in message
...
Thanks Chip, However, It renames all my tabs week1 wee2 ect. and even
changes the Top stock tabs to week13 14 ect.
I tried tweaking it a bit but have not been able to get it to work. Here
are my tabs.
Chart Data Week1 Week2 Week3 Week4 Week5 Week6 Week7(Next week it will go
to Week8 and so on) Then Tabs
Topstockweek1 topstockweek2 topstockweek3 topstockweek4. ect
The code I have from you changed all but chart tab to "week" 1 2 3 4 5
ect..
Thanks for your time
DJ

Macro for changing tabs
 

Sub AddTopStockWeek1()
Dim N As Long
Dim sName As String
Dim sNum As String
Dim itm As Long
With ThisWorkbook.Worksheets
For N = .Count To 1 Step -1
sName = .Item(N).Name
If LCase(Left(sName, 12)) = "topstockweek" Then
sNum = Right(sName, Len(sName) - 12)
If sNum = "1" Then itm = N
.Item(N).Name = "TopStockWeek" & CLng(sNum) + 1
End If
Next N
.Add(befo=.Item(itm)).Name = "TopStockWeek1"
End With

End Sub

Should work.

--
Regards,
Tom Ogilvy



"Duncan J" wrote in message
...
Thanks Tom,
That worked great. Could you do the same for Top Stock out week1
Thanks again.
DJ

Macro for changing tabs
 

Thanks Tom
Couldn't get it work but I have a work around on that one
Thanks agai
DJ

Macro for changing tabs
 

Sorry Tom the tab names ar
Top Stock Out Kits Week1, Top Stock Out Kits Week2, Top Stock Out Kits Week3, and so on
I changed the names in your code and it still wouldn't work. I also changed the tab names in the spreadsheet to match your code. It debugs at this line
..Add(befo=.Item(itm)).Name = "TopStockWeek1
As I said I renamed them to match the tabs in your code but still got the error
Thanks... I'm having a lot of fun with this one.

Macro for changing tabs
 

Sub AddTopStockOutKitsWeek1()
Dim N As Long
Dim sName As String
Dim sNum As String
Dim itm As Long
With ThisWorkbook.Worksheets
For N = .Count To 1 Step -1
sName = .Item(N).Name
If LCase(Left(sName, 23)) = "Top Stock Out Kits Week" Then
sNum = Right(sName, Len(sName) - 23)
itm = N
.Item(N).Name = "Top Stock Out Kits Week" & CLng(sNum) + 1
End If
Next N
if itm = 0 then itm = 1
.Add(befo=.Item(itm)).Name = "Top Stock Out Kits Week1"
End With

Should work if the names are Top Stock Out Kits Week

--
Regards,
Tom Ogilvy


"Duncan J" wrote in message
...
Sorry Tom the tab names are
Top Stock Out Kits Week1, Top Stock Out Kits Week2, Top Stock Out Kits
Week3, and so on.
I changed the names in your code and it still wouldn't work. I also
changed the tab names in the spreadsheet to match your code. It debugs at
this line.
.Add(befo=.Item(itm)).Name = "TopStockWeek1"
As I said I renamed them to match the tabs in your code but still got the
error.
Thanks... I'm having a lot of fun with this one.