Forum Discussion
I want to rename all the sheet tabs of an excel
- Thank you friend.
I have used the above code and it actaully worked but issue arised is as per our procedures we renumber and rename the sheets using a macro button then it would look like this 1-Rob1, 2-Rob2,...10-Rob10,....100-Rob100 now i have inputtted the character as 5 for the old and renamed the result came out missed from10-Rob10 (bRaj10) bcoz it has 6 characters the and the 100-Rob10 has 7chartacters (obRaj100).
I have used the below code but the lapse is it is changing the serial number but it ibrilliantly changes the sheet name just by asking new code....can we adjust the below code accordingly.
Sub ChangeWorkSheetName()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
newName = Application.InputBox(“Name”, xTitleId, “”, Type:=2)
For i = 1 To Application.Sheets.Count
Application.Sheets(i).Name = newName & i
Next
End Sub
Raj_123 without the VBA code we can't tell you exactly what lines to change but in the VBA code there must be something like a loop something like For i = 1 to Activeworkbook.sheets.count
and then an assignment like
Activeworkbook.sheet(i)="Raj"&i
you need to instead do something like
temp=mid(Activeworkbook.sheet(i).name,6)
Activeworkbook.sheet(i)="Raj"&temp
Raj_123 Here is a routine that will do what you ask. It needs to be added to a module in the VBA and will ONLY properly work to rename from the original sheet names of Sheet1, Sheet2, ... and it will maintain the original sheet numbers. But if you rename everything Raj1, Raj2, ... and change your mind and want RAJA instead, this macro would need to be adjusted.
Sub renameSheets() shtName = InputBox("Enter the new name to use for each sheet instead of 'sheet'", "New Sheet Name") If shtName = "" Then Exit Sub End If For i = 1 To ActiveWorkbook.Sheets.Count ActiveWorkbook.Sheets(i).Name = shtName & Mid(ActiveWorkbook.Sheets(i).Name, 6) Next i End Sub- Yes i have to change the sheet names multiple times may i request you to adjust the macro like that.. also, may i please know the purpose of number 6? I actually have n number of sheet tabs..
Raj_123 that 6 was because there are 5 characters in "Sheet" so it added the characters 6 and after.
Here is a different version that lets you pick how many characters to remove before adding the new default name.
Sub renameSheets() chrRemove = InputBox("Enter the number of characters to remove from the existing sheet names. E.g. if you want to remove the default name: 'sheet' then enter 5", "Remove X characters") If chrRemove = "" Then Exit Sub End If shtName = InputBox("Enter the new name to insert on each sheet name", "New Sheet Name") If shtName = "" Then Exit Sub End If On Error GoTo badName For i = 1 To ActiveWorkbook.Sheets.Count ActiveWorkbook.Sheets(i).Name = shtName & Mid(ActiveWorkbook.Sheets(i).Name, chrRemove + 1) Next i Exit Sub badName: ActiveWorkbook.Sheets(i).Name = shtName & Mid(ActiveWorkbook.Sheets(i).Name, chrRemove + 1) & i Err.Clear On Error GoTo badName Resume Next End Sub