Name of Added Worksheet
 

If I add a worksheet as in the code below, can I force the name of the new
sheet to something because this code was working fine on my PC, but on
another user's pc, the added sheet was Sheet4. Instead of changing their
template or whatever might be causing this, can I just force the new sheet to
some name, or can I otherwise determine the name of the new sheet and then go
to it. My next line of code is to rename this sheet1 anyway.

Sheets.Add Type:="Worksheet"
Sheets("Sheet1").Select

Name of Added Worksheet
 

Sub mike()
Sheets.Add
ActiveSheet.Name = "mike"
End Sub

--
Gary''s Student - gsnu200747


"Mike H." wrote:

If I add a worksheet as in the code below, can I force the name of the new
sheet to something because this code was working fine on my PC, but on
another user's pc, the added sheet was Sheet4. Instead of changing their
template or whatever might be causing this, can I just force the new sheet to
some name, or can I otherwise determine the name of the new sheet and then go
to it. My next line of code is to rename this sheet1 anyway.

Sheets.Add Type:="Worksheet"
Sheets("Sheet1").Select

Name of Added Worksheet
 

Try something like

ThisWorkbook.Worksheets.Add.Name = "MyNewName"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Mike H." wrote in message
...
If I add a worksheet as in the code below, can I force the name of the new
sheet to something because this code was working fine on my PC, but on
another user's pc, the added sheet was Sheet4. Instead of changing their
template or whatever might be causing this, can I just force the new sheet
to
some name, or can I otherwise determine the name of the new sheet and then
go
to it. My next line of code is to rename this sheet1 anyway.

Sheets.Add Type:="Worksheet"
Sheets("Sheet1").Select

Name of Added Worksheet
 

Sub addsheetandname()
Sheets.Add
ActiveSheet.Name = "Mike"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mike H." wrote in message
...
If I add a worksheet as in the code below, can I force the name of the new
sheet to something because this code was working fine on my PC, but on
another user's pc, the added sheet was Sheet4. Instead of changing their
template or whatever might be causing this, can I just force the new sheet
to
some name, or can I otherwise determine the name of the new sheet and then
go
to it. My next line of code is to rename this sheet1 anyway.

Sheets.Add Type:="Worksheet"
Sheets("Sheet1").Select

Name of Added Worksheet
 

Hi Mike,

Change your code as

Sheets.Add Type:="Worksheet"
ActiveSheet.Name = "ABCD"

Where 'ABCD' is the name of your new worksheet

HTH,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Mike H." wrote:

If I add a worksheet as in the code below, can I force the name of the new
sheet to something because this code was working fine on my PC, but on
another user's pc, the added sheet was Sheet4. Instead of changing their
template or whatever might be causing this, can I just force the new sheet to
some name, or can I otherwise determine the name of the new sheet and then go
to it. My next line of code is to rename this sheet1 anyway.

Sheets.Add Type:="Worksheet"
Sheets("Sheet1").Select

Name of Added Worksheet
 

Mike,

Sheets.Add.Name = "Whatever You Want"

HTH,
Bernie
MS Excel MVP


"Mike H." wrote in message
...
If I add a worksheet as in the code below, can I force the name of the new
sheet to something because this code was working fine on my PC, but on
another user's pc, the added sheet was Sheet4. Instead of changing their
template or whatever might be causing this, can I just force the new sheet to
some name, or can I otherwise determine the name of the new sheet and then go
to it. My next line of code is to rename this sheet1 anyway.

Sheets.Add Type:="Worksheet"
Sheets("Sheet1").Select

Name of Added Worksheet
 

Sheets.Add Type:="Worksheet"
ActiveSheet.Name = "whatyouwanthere"

(the newly added sheet is the activesheet)

You could also use:
Worksheets.Add.Name = "newsheetnamehere"

But I like this:

Dim NewWks as worksheet
set newwks = worksheets.add
newwks.name = "somename"

But then I can refer to the new worksheet by using the newwks variable. I can
do:

with newwks
.range("a1").value = "hi there"
.protect password:="pwd"
end with

And I don't have to rely on the name at all.

Mike H. wrote:

If I add a worksheet as in the code below, can I force the name of the new
sheet to something because this code was working fine on my PC, but on
another user's pc, the added sheet was Sheet4. Instead of changing their
template or whatever might be causing this, can I just force the new sheet to
some name, or can I otherwise determine the name of the new sheet and then go
to it. My next line of code is to rename this sheet1 anyway.

Sheets.Add Type:="Worksheet"
Sheets("Sheet1").Select

--

Dave Peterson

Name of Added Worksheet
 

Hi Mike
In addition to Gary's student answer, please note that when a sheet is added
it will become the ActiveSheet.

HTH
Cordially
Pascal

"Mike H." a écrit dans le message de news:
...
If I add a worksheet as in the code below, can I force the name of the new
sheet to something because this code was working fine on my PC, but on
another user's pc, the added sheet was Sheet4. Instead of changing their
template or whatever might be causing this, can I just force the new sheet
to
some name, or can I otherwise determine the name of the new sheet and then
go
to it. My next line of code is to rename this sheet1 anyway.

Sheets.Add Type:="Worksheet"
Sheets("Sheet1").Select

Name of Added Worksheet
 

I wasn't sure the newly added sheet would be the active sheet. Knowing that
makes this easy to do. Thanks to all who responded and so quickly.

Name of Added Worksheet
 

Mike,

I wasn't sure the newly added sheet would be the active sheet. Knowing that ....

Be careful - that isn't always true - you can add sheets to workbooks that aren't currently active,
and so the activesheet will not be the just-added sheet. It is better coding practice to change the
name when you add the sheet or to set a worksheet object = to the new sheet, and set the name using
that object (see Dave Peterson's code.)

HTH,
Bernie
MS Excel MVP


"Mike H." wrote in message
...
makes this easy to do. Thanks to all who responded and so quickly.