Name of Added Worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike H. - 27 Sep 2007 14:03 GMT

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

Reply to this Message

Gary''s Student - 27 Sep 2007 14:08 GMT

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

Signature

Gary''s Student - gsnu200747

> 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
[quoted text clipped - 5 lines]
> Sheets.Add Type:="Worksheet"
> Sheets("Sheet1").Select

Reply to this Message

Chip Pearson - 27 Sep 2007 14:08 GMT

Try something like

ThisWorkbook.Worksheets.Add.Name = "MyNewName"

Signature

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

> 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
[quoted text clipped - 7 lines]
> Sheets.Add Type:="Worksheet"
> Sheets("Sheet1").Select

Reply to this Message

Don Guillett - 27 Sep 2007 14:08 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

Reply to this Message

Pranav Vaidya - 27 Sep 2007 14:09 GMT

Hi Mike,

Change your code as

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

Where 'ABCD' is the name of your new worksheet

HTH,

Signature

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

Reply to this Message

Bernie Deitrick - 27 Sep 2007 14:10 GMT

Mike,

Sheets.Add.Name = "Whatever You Want"

HTH,
Bernie
MS Excel MVP

Reply to this Message

Dave Peterson - 27 Sep 2007 14:12 GMT

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.

Signature

Dave Peterson

Reply to this Message

papou - 27 Sep 2007 14:14 GMT

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

Reply to this Message

Mike H. - 27 Sep 2007 14:28 GMT

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.

Reply to this Message

Bernie Deitrick - 27 Sep 2007 14:59 GMT

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

> makes this easy to do. Thanks to all who responded and so quickly.

Reply to this Message