Adding to an existing macro to make Excel rename sheet after specific cell.

[krtsbgfut]

Hello, hope everyone is doing OK. I have been using Microsoft office products for years now. I've only recently discovered however, macros and some of the other "behind the scene" tools available. What I could use a hand with is this; with the help of my son, I have recoded a macro that records the formatting (column width, row height, borders, etc...) from my first worksheet, (I'll call it template) opens a new ws (ws1), and applies said formatting to it. Love that function. The code I want to add is; after creating the new ws1 I would like to have ws1 look at its own cell A1 and rename the worksheet to whatever is in ws1, A1. Then if I use the macro to create another ws(2), I would like the new ws2 to look at its A1 and rename ws2, to what's in ws2, A1. Etc... I will post the code in this thread to show you the macro I've already recorded. I'm hoping to somehow add the code in here somewhere so that the newly created ws will get its name from its own cell A1.
"
Sub Apply_formatting()
'
' Apply_formatting Macro
'
' Keyboard Shortcut: Ctrl+m
'
Cells.Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Cells.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
"
Thank you in advance, and thanks for tolerating my ignorance. (Remember, the difference between ignorance and stupidity; Ignorance is: Having never seen a crocodile before in your life, you reach out to pet it because it's so cute. Stupidity is then offering it your remaining hand)

 

[circledchicken]

Hi and welcome to the forum,

Can you provide a little more detail?
Won't A1 be empty when you create the new worksheets?

 

[ZAX]

Hi and welcome to the forum,

Can you provide a little more detail?
Won't A1 be empty when you create the new worksheets?

CircledChicken is right, Well do you mean you want to name the new created sheets in order such as Ws1,Ws2,Ws3,Ws4,...etc ?!

 

[AlphaFrog]

[color=darkblue]Sub[/color] Copy_Sheet_and_Rename()
    
ActiveSheet.Copy After:=Sheets(Sheets.Count)    [color=green]'Copy worksheet[/color]
[color=darkblue]If[/color] [color=darkblue]Not[/color] IsEmpty(Range("A1")) [color=darkblue]Then[/color]                [color=green]'Test if cell A1 is not empty[/color]
    [color=darkblue]If[/color] [color=darkblue]Not[/color] Evaluate("ISREF('" & Range("A1").Value & "'!A1)") Then  [color=green]'Test if worksheet name already exists[/color]
        ActiveSheet.Name = Range("A1").Value    [color=green]'Rename copied sheet[/color]
    [color=darkblue]Else[/color]
        MsgBox "There is a sheet already named '" & Range("A1").Value & "'", _
               vbExclamation, "Sheet Name Already Exists"
    [color=darkblue]End[/color] [color=darkblue]If[/color]
[color=darkblue]End[/color] [color=darkblue]If[/color]
    
End [color=darkblue]Sub[/color]

 

[krtsbgfut]

Actually, I was trying to get the worksheet to have some sort of code that would change the name of the respective worksheets once A1 was filled in. I'm not sure if I could maybe leave the worksheet name blank (though I highly doubt that) or if I could add code that would name it WS1, WS2, ... by default and then when I do put a label in A1, to update that worksheet's name. I.E. I make my 1st worksheet (template). Then when I hit my macro, I would like it to make a new worksheet (WS1) with all the formatting of Template (which the code I posted earlier takes care of) and call the new worksheet (WS1) something, anything really, and if I chose to add a label in WS1, A1, to update the worksheet's (WS1) name to reflect the label in A1. Then if I hit the macro again, make yet another worksheet (WS2) with the formatting etc... and again, if I decide to input something in WS2, A1, have the code already in place to update the worksheet's name.

 

[Peter_SSs]

My attempt. Test in a copy of your workbook.


1. Right click the "template" sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$A$1" And Target.Parent.Name <> "template" Then
    On Error Resume Next
    Target.Parent.Name = Left(Range("A1").Value, 31)
    On Error GoTo 0
  End If
End Sub

3. In a Standard Module put this code.

Sub New_Sheet()
  Dim i As Long
  
  Sheets("template").Copy After:=Sheets(Sheets.Count)
  Do
    i = i + 1
  Loop While Evaluate("ISREF(" & "WS" & i & "!A1)")
  ActiveSheet.Name = "WS" & i
End Sub

4. Test by running the New_Sheet macro one or more times then altering cell A1 in any of the newly created sheets.

Notes:
a) It would be difficult to keep track of what "WS numbers" had been used in the past. My code uses the lowest "WS number" available. That is, If WS1, WS2 and WS3 have been created and then WS2's name gets changed, the next copy of the template sheet will use WS2 again.

b) It is possible that an invalid sheet name could be entered in cell A1. For example, the name of an already existing name, a value that contains invalid characters for a sheet name, the value in A1 could be deleted. In these cases the sheet name will remain as "WS2" or whatever it was before the invalid A1 entry.

 

[krtsbgfut]

Sir, your a scholar gentlemen, and one heck of a programmer. My 13 year-old was kind enough to take the code you wrote and put it into a new Microsoft 2007 Excel test spreadsheet. At first he copied and pasted it and we got errors, but then realizing I had forgot to tell him to name it "Template." After realizing P.E.B.K.A.C. (Problem exists between keyboard and chair, me not my son) My son caught the mistake, and now ALL is well. Neither my son nor I can thank you enough. Thanks for the help.

 

[Peter_SSs]

Neither my son nor I can thank you enough.

You just have.
Thanks for letting us know.

 

[tusharm]

I see that Peter's solution works for you but IMO you are going about this the roundabout way. There really is no need for code. {grin}

To create a copy of the template do just that. Hold down CTRL, click on the template sheet tab, and drag it until you see a little + cursor. Let go and Excel will create a new worksheet that is a duplicate of template.

If you plan to type in the name of the new worksheet, why not do so in the sheet tab itself?

If you want the sheet name in a cell you can use a formula to get the needful (with the caveat that the workbook must have been saved at least once).

Actually, I was trying to get the worksheet to have some sort of code that would change the name of the respective worksheets once A1 was filled in. I'm not sure if I could maybe leave the worksheet name blank (though I highly doubt that) or if I could add code that would name it WS1, WS2, ... by default and then when I do put a label in A1, to update that worksheet's name. I.E. I make my 1st worksheet (template). Then when I hit my macro, I would like it to make a new worksheet (WS1) with all the formatting of Template (which the code I posted earlier takes care of) and call the new worksheet (WS1) something, anything really, and if I chose to add a label in WS1, A1, to update the worksheet's (WS1) name to reflect the label in A1. Then if I hit the macro again, make yet another worksheet (WS2) with the formatting etc... and again, if I decide to input something in WS2, A1, have the code already in place to update the worksheet's name.

 

[krtsbgfut]

Please forgive me, I by no means meant to exclude anyone else when I was thanking the gentleman known in here as Peter_SSs. I wanted to thank one and all for their help, I just got carried away when the code given to me was successful. Again I want say thanks to everyone who helped.