How to replace tab name in Excel?

Question

I have 20 excel files, each has 36 tabs. I need to replace all the tab names ends with '06 to '07. Is there any easy way to do, instead of rename one by one for 720 times? Thank you!

I will select to run Macro. I will change the last one number at right end in each tab name. For example, "BudgetJan'06" will be "BudgetJan'07". To be easier, I can just detele the "06", keep the tab name as "BudgetJan". Please help to write macro. Thank U!

Answer 1

Run this macro,

Sub rename_tabs()
Dim sht
For Each sht In ActiveWorkbook.Worksheets
If Right(sht.Name, 2) = "06" Then
sht.Name = Right(sht.Name, Len(sht.Name) - 2) & "07"
End If
Next
End Sub

Answer 2

Create a macro to do it. I assume they all have the base name? Meaning that in each of the 20 files, the 36 tabs have the same name up to the "06" portion. If so, a simple macro such as the following would do it quickly:

Sub RenameTabs()
'
Sheets("mySheet1-06").Select
Sheets("mySheet1-06").Name = "mySheet1-07"
Sheets("mySheet2-06").Select
Sheets("mySheet2-06").Name = "mySheet2-07"
'
' put the rest here...
Sheets("mySheet36-06").Select
Sheets("mySheet36-06").Name = "mySheet36-07"

End Sub

To create your macro, do the following:
1) Select Tools > Macro > Macros... (or alt-f8)
2) Enter a name such as RenameTabs in the name field and press Create
3) Cut the above function into the opened editor window
4) Select File > Close and return...
5) Press Alt-F8 again, select the macro, and then press run.

You could then expand this macro to open up the other 19 files and do the same thing.

Answer 3

I only know about changing the actual tab individually. When I deal with dates. I always use headers or footers as these can be changed right across the board. I find this easier. I usually don't have any problem with this.

You can include the name of the sheet with date when you print the worksheet. On the View menu, click Header and Footer, and then, in the Page Setup dialog box on the Header/Footer tab, click Custom Header or Custom Footer. In the Left section, Center section, or Right section box, enter the sheet tab by clicking Tab in the row of buttons in the Header or Footer dialog box. Not sure if this can help you or not. Trial and Error, I find.

Answer 4

Unfortunately there is not... I was thinking that you could select all sheets and change it that way but I tried it and it doesn't work... Sorry!

EDIT: Macros! Completely forgot about macros! Listen to the guy below me! :-)

Answer 5

For that size of data, you'd be better off using a database rather than a spreadsheet which would make even administrative tasks like the one you raise in your question a lot easier to deal with.

Maybe someone can suggest a script that would do it but not sure....

Answer 6

place the cursor the place you're able to like the entire after which you would be able to click on the formulation sign interior the icon gadget bar. click in this and it will automaticly do the formulation for you. or you are able to type =sum(A1:A16) and this could upload up each and all of the suggestions for you. or you are able to type =sum( then click on the 1st cellular you like interior the formulation and drag the mouse down highlighting each and all of the cells you require. Then type ) and hit enter. wish this has helped.

Answer 7

I don't think that there is a shortcut to do that.