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



"Price" wrote in message
om...
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.

Try this Price

Sub test()
With ThisWorkbook
If .Worksheets.Count 2 Then
.Worksheets.Add Befo=.Worksheets(.Worksheets.Count - 2)
.ActiveSheet.Name = .Worksheets("Sheet1").Range("a1")
End If
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Price" wrote in message om...
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.

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 :

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

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 :)


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements