SOLVED

I want to rename all the sheet tabs of an excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1631642%22%20slang%3D%22en-US%22%3EI%20want%20to%20rename%20all%20the%20sheet%20tabs%20of%20an%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1631642%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3EIf%20there%20are%20sheet%201%2C%20sheet%202%2C%20sheet3%20%2C%20sheet4%20and%20sheet5%20and%20i%20have%20deleted%20the%20sheet%204%20tab...now%20the%20sheets%20in%20excel%20would%20be%20sheet1%2C%20sheet2%2C%20sheet3%20and%20sheet5..(%20please%20observe%20there%20would%20be%20no%20sheet4%20as%20i%20have%20deleted)%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20VBA%20code%20and%20used%20to%20rename%20(renamed%20as%20'Raj')%20all%20the%20sheets%20at%20one%20go%20but%20the%20result%20i%20am%20getting%20is%20Raj1%2CRaj2%2CRaj3%2CRaj4%20however%2C%20i%20want%20the%20result%20as%20Raj1%2CRaj2%2CRaj3%2CRaj5.%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20to%20do%20this%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1631642%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1631752%22%20slang%3D%22en-US%22%3ERe%3A%20I%20want%20to%20rename%20all%20the%20sheet%20tabs%20of%20an%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1631752%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F780831%22%20target%3D%22_blank%22%3E%40Raj_123%3C%2FA%3E%26nbsp%3B%20without%20the%20VBA%20code%20we%20can't%20tell%20you%20exactly%20what%20lines%20to%20change%20but%20in%20the%20VBA%20code%20there%20must%20be%20something%20like%20a%20loop%20something%20like%20For%20i%20%3D%201%20to%20Activeworkbook.sheets.count%3C%2FP%3E%3CP%3Eand%20then%20an%20assignment%20like%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BActiveworkbook.sheet(i)%3D%22Raj%22%26amp%3Bi%3C%2FP%3E%3CP%3Eyou%20need%20to%20instead%20do%20something%20like%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3Btemp%3Dmid(Activeworkbook.sheet(i).name%2C6)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BActiveworkbook.sheet(i)%3D%22Raj%22%26amp%3Btemp%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1632012%22%20slang%3D%22en-US%22%3ERe%3A%20I%20want%20to%20rename%20all%20the%20sheet%20tabs%20of%20an%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1632012%22%20slang%3D%22en-US%22%3EMay%20i%20please%20have%20entire%20VBA%20code%20that%20you%20are%20refering%20to%3F%20Instead%20of%20Raj%2C%20i%20want%20macro%20need%20to%20ask%20me%20a%20name%20for%20all%20the%20time%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1633199%22%20slang%3D%22en-US%22%3ERe%3A%20I%20want%20to%20rename%20all%20the%20sheet%20tabs%20of%20an%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1633199%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F780831%22%20target%3D%22_blank%22%3E%40Raj_123%3C%2FA%3E%26nbsp%3B%20Here%20is%20a%20routine%20that%20will%20do%20what%20you%20ask.%26nbsp%3B%20It%20needs%20to%20be%20added%20to%20a%20module%20in%20the%20VBA%20and%20will%20ONLY%20properly%20work%20to%20rename%20from%20the%20original%20sheet%20names%20of%20Sheet1%2C%20Sheet2%2C%20...%20and%20it%20will%20maintain%20the%20original%20sheet%20numbers.%26nbsp%3B%20But%20if%20you%20rename%20everything%20Raj1%2C%20Raj2%2C%20...%20and%20change%20your%20mind%20and%20want%20RAJA%20instead%2C%20this%20macro%20would%20need%20to%20be%20adjusted.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20renameSheets()%0A%20%20%20%20shtName%20%3D%20InputBox(%22Enter%20the%20new%20name%20to%20use%20for%20each%20sheet%20instead%20of%20'sheet'%22%2C%20%22New%20Sheet%20Name%22)%0A%20%20%20%20If%20shtName%20%3D%20%22%22%20Then%0A%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20End%20If%0A%20%20%20%20For%20i%20%3D%201%20To%20ActiveWorkbook.Sheets.Count%0A%20%20%20%20%20%20%20%20ActiveWorkbook.Sheets(i).Name%20%3D%20shtName%20%26amp%3B%20Mid(ActiveWorkbook.Sheets(i).Name%2C%206)%0A%20%20%20%20Next%20i%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1633236%22%20slang%3D%22en-US%22%3ERe%3A%20I%20want%20to%20rename%20all%20the%20sheet%20tabs%20of%20an%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1633236%22%20slang%3D%22en-US%22%3EYes%20i%20have%20to%20change%20the%20sheet%20names%20multiple%20times%20may%20i%20request%20you%20to%20adjust%20the%20macro%20like%20that..%20also%2C%20may%20i%20please%20know%20the%20purpose%20of%20number%206%3F%20I%20actually%20have%20n%20number%20of%20sheet%20tabs..%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1633794%22%20slang%3D%22en-US%22%3ERe%3A%20I%20want%20to%20rename%20all%20the%20sheet%20tabs%20of%20an%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1633794%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F780831%22%20target%3D%22_blank%22%3E%40Raj_123%3C%2FA%3E%26nbsp%3B%20that%206%20was%20because%20there%20are%205%20characters%20in%20%22Sheet%22%20so%20it%20added%20the%20characters%206%20and%20after.%3C%2FP%3E%3CP%3EHere%20is%20a%20different%20version%20that%20lets%20you%20pick%20how%20many%20characters%20to%20remove%20before%20adding%20the%20new%20default%20name.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20renameSheets()%0A%20%20%20%20chrRemove%20%3D%20InputBox(%22Enter%20the%20number%20of%20characters%20to%20remove%20from%20the%20existing%20sheet%20names.%20E.g.%20if%20you%20want%20to%20remove%20the%20default%20name%3A%20'sheet'%20then%20enter%205%22%2C%20%22Remove%20X%20characters%22)%0A%20%20%20%20If%20chrRemove%20%3D%20%22%22%20Then%0A%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20End%20If%0A%20%20%20%20shtName%20%3D%20InputBox(%22Enter%20the%20new%20name%20to%20insert%20on%20each%20sheet%20name%22%2C%20%22New%20Sheet%20Name%22)%0A%20%20%20%20If%20shtName%20%3D%20%22%22%20Then%0A%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20End%20If%0A%20%20%20%20On%20Error%20GoTo%20badName%0A%20%20%20%20For%20i%20%3D%201%20To%20ActiveWorkbook.Sheets.Count%0A%20%20%20%20%20%20%20%20ActiveWorkbook.Sheets(i).Name%20%3D%20shtName%20%26amp%3B%20Mid(ActiveWorkbook.Sheets(i).Name%2C%20chrRemove%20%2B%201)%0A%20%20%20%20Next%20i%0A%20%20%20%20Exit%20Sub%0AbadName%3A%0A%20%20%20%20ActiveWorkbook.Sheets(i).Name%20%3D%20shtName%20%26amp%3B%20Mid(ActiveWorkbook.Sheets(i).Name%2C%20chrRemove%20%2B%201)%20%26amp%3B%20i%0A%20%20%20%20Err.Clear%0A%20%20%20%20On%20Error%20GoTo%20badName%0A%20%20%20%20Resume%20Next%0A%20%20%20%20%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Contributor
Hi
If there are sheet 1, sheet 2, sheet3 , sheet4 and sheet5 and i have deleted the sheet 4 tab...now the sheets in excel would be sheet1, sheet2, sheet3 and sheet5..( please observe there would be no sheet4 as i have deleted)

I have a VBA code and used to rename (renamed as 'Raj') all the sheets at one go but the result i am getting is Raj1,Raj2,Raj3,Raj4 however, i want the result as Raj1,Raj2,Raj3,Raj5.

How to do this?
10 Replies

@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

 

 

May i please have entire VBA code that you are refering to? Instead of Raj, i want macro need to ask me a name for all the time

@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
Best Response confirmed by Raj_123 (Contributor)
Solution
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  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.

Thank you my bestie...