Name of Added Worksheet

M

Mike H.

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
 
C

Chip Pearson

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

Pranav Vaidya

Hi Mike,

Change your code as

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

Where 'ABCD' is the name of your new worksheet

HTH,
 
D

Dave Peterson

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.
 
P

papou

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
 
M

Mike H.

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.
 
B

Bernie Deitrick

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top