Solved

Renaming a sheet tab

Posted on 2014-10-22
12
134 Views
Last Modified: 2014-10-29
How do I create and new sheet tab, and name it with whatever the value is of the active cell?
I recorded the following and even with the "Use Relative References" on, it still used static values when I tried to copy and paste in the sheet tab renaming...

Sub Macro3()
    'I am starting the process with a cell selected that
    'will become the new sheet tabs name.  I copy the cell
    'and Ln3 is the paste into the sheet tab rename.

    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet3").Select 'will be different each time
    Sheets("Sheet3").Name = "120-2758"  'will be different each time
    Sheets("ReadMe").Select
    ActiveCell.Rows("1:1").EntireRow.Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
End Sub

Open in new window

0
Comment
Question by:RWayneH
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40397932
I have a code exactly  as described, I will look tomorrow in my archive and will get back with solution .
0
 
LVL 33

Expert Comment

by:Norie
ID: 40397947
Put the new sheet name into a variable before you add the new sheet.
Dim wsNew As Worksheet 
Dim strShName As String

    strShName = ActiveCell.Value

    Set wsNew = Worksheets.Add

     wsNew.Name = strShName

Open in new window

0
 

Author Comment

by:RWayneH
ID: 40397967
Had issues place those lines into the recorded code...   getting compile errors.
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 250 total points
ID: 40398005
Sub AddSheet()
Dim wsNew As Worksheet
Dim strShName As String

    If ActiveCell.Value <> "" Then
        strShName = ActiveCell.Value
   
        Set wsNew = Worksheets.Add
   
        wsNew.Name = strShName
    Else
        Msgbox "Active cell is empty"
    End If
End Sub
0
 
LVL 33

Expert Comment

by:Norie
ID: 40398392
No compile errors when I tested the code, the only problem I can foresee is the active cell being empty or having an invalid sheet name.:)
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40398440
My code checks for blanks but imnorie makes a good point about invalid characters. I believe that the invalid characters are / ? * [ ] The name can also not exceed not exceed 31 characters.
0
 

Assisted Solution

by:RWayneH
RWayneH earned 0 total points
ID: 40399286
Thanks that will work....  here is what I landed on.  I added a For Selection so it would go through a list and create them all at once, however it fails if there is a dup.  How would I say that on error skip that and continue onto the next one.  The failure could be an invalid char or to many chars or a dup sheets tab....  etc.  

How would I add that to this?

Sub AddSheet()
 Dim wsNew As Worksheet
 Dim strShName As String


    For Each c In Selection  'C is a list is hightlighted in excel
    If c.Value = "" Then Exit For  'when end of list is reached, exit. no End If with the Then statement

    If ActiveCell.Value <> "" Then
         'strShName = ActiveCell.Value
         strShName = c.Value
         Set wsNew = Worksheets.Add
     
         wsNew.Name = strShName
    End If
    Sheets("ReadMe").Select
Next

 End Sub

Open in new window

0
 

Author Comment

by:RWayneH
ID: 40399466
Thanks ProfessorJimJam.  I like mine better that will create based on a selection or list.  We are just trying to figure out how to address errors that can occur when a dup, bad char, or too many chars are used to create the sheet.
0
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 250 total points
ID: 40399753
RWayneH,

here you go your code.  takes care of everything you have asked.

Sub AddSheet()
 
   Dim wsNew As Worksheet
   Dim strShName As String
   Dim strSheetName As String, wks As Worksheet, bln As Boolean

    For Each c In Selection
    If IsEmpty(c.Value) Then GoTo SkipToHere:

    

     If Len(c.Value) > 31 Then GoTo SkipToHere:
    
    
    Dim IllegalCharacter(1 To 7) As String, i As Integer
    IllegalCharacter(1) = "/"
    IllegalCharacter(2) = "\"
    IllegalCharacter(3) = "["
    IllegalCharacter(4) = "]"
    IllegalCharacter(5) = "*"
    IllegalCharacter(6) = "?"
    IllegalCharacter(7) = ":"
    For i = 1 To 7
        If InStr(c.Value, (IllegalCharacter(i))) > 0 Then GoTo SkipToHere:
     Next i

strSheetName = Trim(c.Value)
    On Error Resume Next
    Set wks = ActiveWorkbook.Worksheets(strSheetName)
If Err.Number = 0 Then GoTo SkipToHere:
   
       
    If c.Value <> "" And Not InStr(c.Value, (IllegalCharacter(i))) > 0 Then

         strShName = c.Value
         Set wsNew = Worksheets.Add

         wsNew.Name = strShName
    End If
       'Sheets("ReadMe").Select
SkipToHere:
Next
End Sub

Open in new window

0
 

Author Comment

by:RWayneH
ID: 40401950
One small issue maybe that if one in the selection is skipped and not created... there is no way of knowing.  Perhaps we could have the cell skipped get a background color?  This would at least highlight the issue.  Hope it is not too late to add that?
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40409856
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
 

Author Closing Comment

by:RWayneH
ID: 40410223
Thanks for the help.  EXCELent!!
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question