Sep 03 2020 05:56 AM
Sep 03 2020 06:16 AM
@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
Sep 03 2020 07:32 AM
Sep 03 2020 01:18 PM
@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
Sep 03 2020 01:35 PM
Sep 03 2020 03:56 PM
@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
Sep 04 2020 04:07 AM
SolutionSep 04 2020 09:46 AM
@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.
Sep 04 2020 10:13 AM
Sep 04 2020 10:25 AM
@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.
Sep 04 2020 04:07 AM
Solution