Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for changing tabs
Thanks Tom
Couldn't get it work but I have a work around on that one Thanks agai DJ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart tabs changing by themself | Charts and Charting in Excel | |||
Run Macro when changing tabs | Excel Discussion (Misc queries) | |||
Changing the size of sheetname tabs | Excel Discussion (Misc queries) | |||
Changing Tabs to Specific Cells | Excel Discussion (Misc queries) | |||
Changing colors of tabs | Excel Discussion (Misc queries) |