Need help converting a recorded macro into workable VBA to add a new sheet at the end of a workbook

[jft1768]

I have a button with a macro assigned. The macro needs to create a new worksheet at the end of the workbook, rename the new sheet to "New County", make the cell size match the rest of the workbook, and then paste in a template from another sheet called "Master".

I recorded a macro that does this, but it has a static reference to the new sheet instead of what ever the new sheet is. So the Macro works one time, but never again. I'm sure this is an easy fix, and perhaps there is a much easier way to do this, but my limited VBA knowledge is hampering me. Any help would be appreciated. Thanks!

Sub AddNewCounty()
'
' AddNewCounty Macro
' Adds a new County sheet to the workbook.
'

'
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Templates").Select
    Range("A15").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "New County"
    Sheets("Master").Select
    Range("A1:AN19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("New County").Select
    Cells.Select
    Selection.ColumnWidth = 3.57
    Range("A1").Select
    ActiveSheet.CheckBoxes.Add(293.25, 31.5, 44.25, 15).Select
    ActiveSheet.CheckBoxes.Add(360.75, 31.5, 42.75, 15).Select
    ActiveSheet.CheckBoxes.Add(428.25, 31.5, 44.25, 15).Select
    ActiveSheet.CheckBoxes.Add(495, 30.75, 45, 15).Select
    ActiveSheet.CheckBoxes.Add(562.5, 30, 45, 15.75).Select
    ActiveSheet.CheckBoxes.Add(630, 31.5, 88.5, 14.25).Select
    ActiveSheet.Buttons.Add(787.5, 1.5, 113.25, 78).Select
    ActiveSheet.Buttons.Add(787.5, 94.5, 112.5, 78).Select
    ActiveSheet.Buttons.Add(787.5, 188.25, 111.75, 78.75).Select
    ActiveSheet.DropDowns.Add(585.75, 15.75, 133.5, 15).Select
    ActiveSheet.Paste
End Sub

 

[taurean]

You can not have two sheets with the same name i.e. "New County". So second time the code will error out for sure, there must be some logic for renaming the latest sheet. Please explain.

 

[par60056]

There is a simple fix. Rename the sheet as soon as you add it.

When a sheet is added, it becomes the active sheet. So right after:

Sheets.Add After:=Sheets(Sheets.Count)

put the line:

activesheet.Name = "New County"

or you can name it as you add it like this:

Sheets.Add(After:=Sheets(Sheets.Count)).name = "New County"

Then change references to Sheet3 to New County

 

[par60056]

Taurean,
I would assume the person filling in the template for the "New County" will rename the sheet with the pertinent name

 

[James Snyder]

Another way would be to add something that changes to the new sheet name like a timestamp, an increment, or the new county's name. If added in a loop, and increment based on the loop would work.

 

[jft1768]

Yes the "New County" sheet will be renamed through a Macro that I have linked to a cell on the sheet. As soon as data is entered into the new sheet, the sheet name changes to the name of the county. The code seems to die out where Excel names the new sheet a sheet number (like "Sheet3" as seen in the code above.) I think I need it to just find the last sheet in the workbook and rename it, and not have a specific target like "Sheet3". Unfortunately I don't know exactly how to go about doing that.

 

[jft1768]

AHA! That did it, but I just discovered a problem. When I copy over the cells from the master template (the sheet labeled "Master") the macros don't stay bound to the buttons and the cell that is supposed to rename the sheet doesn't transfer either. :-/

 

[par60056]

Would it make more sense to just copy the entire template sheet?

Sheets("Templates").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).name = "New County"

 

[taurean]

To be sure, you are working on the sheet added you can use following:

Dim wsNew As Worksheet
Set wsNew = Sheets.Add(After:=Sheets(Sheets.Count))

And then instead of using:

Sheets("Sheet3").Name = "New County"

you can implement:

wsNew.Name = "New County"

 

[jft1768]

Thank you par60056! That is indeed the solution. Once again I was trying to make something simple into something way more complicated than it needed to be.