Get the name of a newly copied worksheet
 

I am trying to copy a worksheet then make it active as shown in the code below, but the Set command
does not work. Is this the correct way to grab the identity of a newly copied worksheet? The
worksheet "Invoice Template" is hidden, so that's why the ".Visible" statement.

Set newSheet = Worksheets("Invoice Template").Copy befo=Worksheets(1)

With newSheet
.select
.Visible = xlSheetVisible

result = InputBox("Enter a name for your new invoice", "Name your invoice")
If result < "" Then
.Name = result
Else
.Name = "Invoice - " & Format(Date, "mmm_d_yyyy")
End If
end with


Thank you, Richard

Get the name of a newly copied worksheet
 

Try this:

Code
-------------------
Sub test()

Dim result As String

Worksheets("Sheet3").Copy befo=Worksheets(1)

With ActiveSheet
.Visible = xlSheetVisible

result = InputBox("Enter a name for your new invoice", "Name your invoice")
If result < "" Then
.Name = result
Else
.Name = "Invoice - " & Format(Date, "mmm_d_yyyy")
End If
End With

End Su
-------------------

Basically, when you make a copy of a worksheet it becomes th
ActiveSheet, so you can just do that. Also, your code errored on th
.select statement since the sheet was not visible. The .select shoul
go after .visible. I took it out since the sheet is already selected.

Also, you may need to add some code to make sure that the user doesn'
enter a name that already exists...



--
Message posted from http://www.ExcelForum.com

Get the name of a newly copied worksheet
 

Richard,

Try thus

Worksheets("Invoice Template").Copy befo=Worksheets(1)
Set newSheet = ActiveSheet


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Richard" wrote in message
news:32akc.42981$0u6.7133357@attbi_s03...
I am trying to copy a worksheet then make it active as shown in the code
below, but the Set command
does not work. Is this the correct way to grab the identity of a newly
copied worksheet? The
worksheet "Invoice Template" is hidden, so that's why the ".Visible"
statement.

Set newSheet = Worksheets("Invoice Template").Copy befo=Worksheets(1)

With newSheet
.select
.Visible = xlSheetVisible

result = InputBox("Enter a name for your new invoice", "Name your
invoice")
If result < "" Then
.Name = result
Else
.Name = "Invoice - " & Format(Date, "mmm_d_yyyy")
End If
end with


Thank you, Richard

Get the name of a newly copied worksheet
 

Some quick examples in
http://www.mvps.org/dmcritchie/excel...tm#sheetcoding
and in the topic above that.

When you add a new sheet that sheet becomes the active sheet.
So you want to be able to provide the facility to refer to both,
get the name of your existing sheet before you copy, and the
name of the newsheet after you copy.

A more complete example in
http://www.mvps.org/dmcritchie/excel/bus_sched.htm

something like, you want to put the year before month and
day so you can see them in order in your directory.
I guess if you are copying a specific sheet you
don't need to know the name of the current sheet
as you're not copying it. (so maybe the other
examples you just got fit better).

Dim wsSource As Worksheet
Dim wsNew As Worksheet
Set wsSource = ActiveSheet
Sheets("Invoice Template").Copy befo=Worksheets(1)
Set wsNew = ActiveSheet
wsNew.name = "Invoice - " & Format(Date, "yyyy-mm-dd")

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Richard" wrote in message news:32akc.42981$0u6.7133357@attbi_s03...
I am trying to copy a worksheet then make it active as shown in the code below, but the Set command
does not work. Is this the correct way to grab the identity of a newly copied worksheet? The
worksheet "Invoice Template" is hidden, so that's why the ".Visible" statement.

Set newSheet = Worksheets("Invoice Template").Copy befo=Worksheets(1)

With newSheet
.select
.Visible = xlSheetVisible

result = InputBox("Enter a name for your new invoice", "Name your invoice")
If result < "" Then
.Name = result
Else
.Name = "Invoice - " & Format(Date, "mmm_d_yyyy")
End If
end with


Thank you, Richard

Get the name of a newly copied worksheet
 

You have to make the hidden worksheet "Invoice Template" visible first,
before you can copy it (at least in Excel 2000 SP-3). You might also have to
copy the worksheet, then Set newSheet in a separate statement following the
statement that copies the worksheet.

Set wsTemplate = Worksheets("Invoice Template")

With wsTemplate
.Visible = xlSheetVisible 'Make Invoice Template visible.
.Copy Befo=Worksheets(1)
'newSheet is now Sheet1; previous Sheet1 should now be Sheet2.
Set newSheet = Worksheets(1)
.Visible = xlSheetHidden
End With

With newSheet
'Remaining code here.
End With

Also, the .Select statement after the "With newSheet" should probably be
..Activate
Single step through your code and verify these object variables in the Local
window as you go.
(You "Select" ranges, but you "Activate" worksheets and the activecell.)
--
Regards,
Bill


"Richard" wrote in message
news:32akc.42981$0u6.7133357@attbi_s03...
I am trying to copy a worksheet then make it active as shown in the code
below, but the Set command
does not work. Is this the correct way to grab the identity of a newly
copied worksheet? The
worksheet "Invoice Template" is hidden, so that's why the ".Visible"
statement.

Set newSheet = Worksheets("Invoice Template").Copy befo=Worksheets(1)

With newSheet
.select
.Visible = xlSheetVisible

result = InputBox("Enter a name for your new invoice", "Name your
invoice")
If result < "" Then
.Name = result
Else
.Name = "Invoice - " & Format(Date, "mmm_d_yyyy")
End If
end with


Thank you, Richard