Renaming Worksheet Tabs Macro

[kajero]

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-comfficeffice" /><o></o>
The only worksheet names needed are below. <o></o>
<o></o>
TBL NPL NGRPL<o></o>
TBL NPL NIAU7<o></o>
TBL NPL NIAU8<o></o>
TBL NPL NIA10<o></o>
TBL NPL NNDU4<o></o>

This is the extent of my ability:

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

 

[Joe4]

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:

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

 

[kajero]

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!

 

[Joe4]

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!

 

[kajero]

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

 

[Joe4]

In VBA, to get the current date uses dashes instead of slashes, use something like:

Format(Date, "mm-dd-yyyy")

 

[kajero]

Uhm, where do I put this? I tried to do a nested formula but was really unsucessful.

 

[Joe4]

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")
...

 

[kajero]

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.

 

[Joe4]

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.