Macro to insert and name a new worksheet

  • Thread starter Thread starter Price
  • Start date Start date
P

Price

I need a macro to insert a new sheet in the n-3 position where n is
the total number of worksheets. And I need to name the sheet the
name that is in column A of the current row. Example: A1 has Product
A then I hit a button in column G1 and it creates a page called
Product A Plan in the n-3 location.

Your help is appreciated.
 
Price,

Try the following code

With Worksheets
.Add(after:=.Item(.Count - 2)).Name = ActiveSheet.Range("A1")
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Try this Price

Sub test()
With ThisWorkbook
If .Worksheets.Count > 2 Then
.Worksheets.Add Before:=.Worksheets(.Worksheets.Count - 2)
.ActiveSheet.Name = .Worksheets("Sheet1").Range("a1")
End If
End With
End Sub
 
First, make sure you have added a command button where you want it.
Then, here is a macro to be cut and paste into the worksheet with you
command button (and macro assumes it is CommandButton1). Lots of way
err out on a sheet name, so I put in a generic error handler to tel
you if the name could not be updated automatically.


Private Sub CommandButton1_Click()
On Error GoTo NoName
Dim strNewWorksheet As String

strNewWorksheet = Range("A1").Value & " Plan"
Worksheets.Add.Move after:=Worksheets(Worksheets.Count - 3)
ActiveSheet.Name = strNewWorksheet
NoName:
MsgBox "Unable to rename new sheet."
End Sub

Hope this helps and let me know if you have any trouble with this.


JerryG :
 
Cut and paste this one instead . . . the other did not have the Exit Sub
line to avoid the error message if renaming the sheet was successful .
.. . sorry 'bout that!


Private Sub CommandButton1_Click()
On Error GoTo NoName
Dim strNewWorksheet As String
strNewWorksheet = Range("A1").Value & " Plan"
Worksheets.Add.Move after:=Worksheets(Worksheets.Count - 3)
ActiveSheet.Name = strNewWorksheet
Exit Sub
NoName:
MsgBox "Unable to rename new sheet."
End Sub


JerryG :)
 
Back
Top