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 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 SubI 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 I'm sorry, no. I am completely lost what you want and honestly feel like you are taking advantage of me and this system to do you contract work. My understanding of this message board and why I VOLUNTER here is to help people struggling with Excel; not to do the work for them.
Your last post shows VBA code that looks exactly like what I expected from your original post to which I gave advice how to change, sight unseen. I then wrote code for you to use based on what you said you wanted/needed. Then you changed what your needs were, and I wrote modified code. Now you have additional changes and needs. I am sorry but I have to cut this off as I said, I volunteer to HELP not to DO your project, and I feel I have bent way over that line already.
Best of luck.
- Hi mtarler,
I did understand the pain u went on..but friend, i have learnt a lot from your recent solutions...i bow to you, and you are very very helpful and super talented....after running the macro code i am testing the combinations with the other macros that are already builtin that is why new queries arised for me...
As i am pretty new i could not incorporate the additional loop that you have given in your first reply that is why i asked you to give me the entire code.
I am not doing any project and i am learning and trying the new things.
I remember your support lifelong and i agree that you have helped me more than i expected...
Kudos to you 🙂
All the best.Raj_123 I am very happy to hear you have learned a lot. I hope your project goes well. If you have any other problems or errors you can't figure out we are always here.
As a tip, it works best when you share the worksheet and VBA you have and what the problem is in the most complete terms as you can. Then we can help fix the problem and sometimes can offer suggestions for better ways to do it.
all the best.