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

krtsbgfut

New Member
Joined
Jun 28, 2013
Messages
4
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

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
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

Well-known Member
Joined
Jul 5, 2012
Messages
715
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
Code:
[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]
 
Last edited:

krtsbgfut

New Member
Joined
Jun 28, 2013
Messages
4
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,190
Office Version
365
Platform
Windows
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.
Code:
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.
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

New Member
Joined
Jun 28, 2013
Messages
4
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.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,007
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

New Member
Joined
Jun 28, 2013
Messages
4
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.
 

Forum statistics

Threads
1,081,560
Messages
5,359,604
Members
400,538
Latest member
leon_oscar

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top