Renaming Worksheet Tabs Macro

kajero

Board Regular
Joined
Jun 28, 2009
Messages
150
I would like to create a macro that would bring up an input box or preferably a list box that will allow me to input information for a sheet/tab name where where "TBL NPL NGRPL" appears in the code at the end of this message. The macro needs to be available to any new file created

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
The only worksheet names needed are below. <o:p></o:p>
<o:p></o:p>
TBL NPL NGRPL<o:p></o:p>
TBL NPL NIAU7<o:p></o:p>
TBL NPL NIAU8<o:p></o:p>
TBL NPL NIA10<o:p></o:p>
TBL NPL NNDU4<o:p></o:p>

This is the extent of my ability:

Sub Macro1()
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "TBL NPL NGRPL"
Range("A1").Select
End Sub<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p>I created six macros, but there must be an easier way.</o:p>
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Here is a macro that I think will do what you want. It changes the name of whatever worksheet you are on when you run this macro:

Code:
Sub RenameCurrentWorksheet()
 
    Dim mySheetName
    Dim myOption1 As String
    Dim myOption2 As String
    Dim myOption3 As String
    Dim myOption4 As String
    Dim myOption5 As String
    
'   Set options
    myOption1 = "TBL NPL NGRPL"
    myOption2 = "TBL NPL NIAU7"
    myOption3 = "TBL NPL NIAU8"
    myOption4 = "TBL NPL NIA10"
    myOption5 = "TBL NPL NNDU4"
    
'   Input box
    mySheetName = InputBox("1 - " & myOption1 & vbCrLf & _
                "2 - " & myOption2 & vbCrLf & _
                "3 - " & myOption3 & vbCrLf & _
                "4 - " & myOption4 & vbCrLf & _
                "5 - " & myOption5, _
                "Select Number to Rename Worksheet")
                
'   Set sheet name
    On Error GoTo catch_error
    Select Case mySheetName
        Case 1
            ActiveSheet.Name = myOption1
        Case 2
            ActiveSheet.Name = myOption2
        Case 3
            ActiveSheet.Name = myOption3
        Case 4
            ActiveSheet.Name = myOption4
        Case 5
            ActiveSheet.Name = myOption4
        Case Else
            MsgBox "Invalid entry, sheet name not changed"
    End Select
    Exit Sub
    
catch_error:
    If Err.Number = 1004 Then
        MsgBox "You already have a sheet with this name in this workbook!", vbOKOnly, "RENAME CANCELLED"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
                 
End Sub
 
Upvote 0
Thank you. This is wonderful. I even understand enough of the code so if I want to want to modifiy it for some other redundant projects I can! I just wish I had the ability to write the code initially. Thanks again!
 
Upvote 0
If you keep at it, you'll get there! The Macro Recorder is a great tool to get started, but it can only take you so far.

I tried to document and lay out the code in such a way that you would be able to make minor modifications to it and maintain yourself, so I'm glad to hear that you are able to do so.

Happy Excelling!
 
Upvote 0
I've tried to add the date in the tab by using another sheet with the forumual =date() but I can't replace the "/" with "-" so I can copy it to the work sheet tabl. Can you help me with this? Thanks, Kathy
 
Upvote 0
In VBA, to get the current date uses dashes instead of slashes, use something like:

Format(Date, "mm-dd-yyyy")
 
Upvote 0
Where do you want it to be? If you just want to tack it on to the end of the tab names we created in the original question, then just tack it on to the end of each formula, like:

ActiveSheet.Name = myOption1 & Format(Date, "mm-dd-yyyy")
...
 
Upvote 0
Wow! Thanks!

Can you recommend a good book so I can learn enough VBA code to do simple things? I know how to modify code if I want to change little things, but things like "Active.sheet" are beyond me.
 
Upvote 0
Years ago, I bought "Beginning Access 2000 VBA" written by David Sussman and Robert Smith (from Wrox Press).

But I also learned an awful lot by reading posts here, and be searching the net.
 
Upvote 0

Forum statistics

Threads
1,224,454
Messages
6,178,766
Members
452,875
Latest member
Disastrouscoder

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top