When I add a new worksheet, how do I select it? I don't know which
number it is, since I don't know how many sheets there are. Do I have
to count the sheets to find out how many there are, then add the
worksheet and select the next number higher than the count?
Thanks for the help!

if you use

activeworkbook.Sheets.Add

then the added sheet should become the active / selected one.

Duncan

davegb wrote:

When I add a new worksheet, how do I select it? I don't know which
number it is, since I don't know how many sheets there are. Do I have
to count the sheets to find out how many there are, then add the
worksheet and select the next number higher than the count?
Thanks for the help!

or..

Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "AddedSheet"

then you know the name of the added sheet


Duncan wrote:

if you use

activeworkbook.Sheets.Add

then the added sheet should become the active / selected one.

Duncan

davegb wrote:

When I add a new worksheet, how do I select it? I don't know which
number it is, since I don't know how many sheets there are. Do I have
to count the sheets to find out how many there are, then add the
worksheet and select the next number higher than the count?
Thanks for the help!

When you add a sheet, it is automaticly selected.

Sub AddSheet()
Sheets.Add
End Sub

If you have a large workbook and you want a list of all sheetnames:

Sub List()
Dim ws As Worksheet
Sheets("List").Activate
Range("A1").Activate
For Each ws In Worksheets
ActiveCell.Formula = ws.Name
ActiveCell.Offset(1, 0).Select
Next
End Sub


"davegb" schreef in bericht
oups.com...
When I add a new worksheet, how do I select it? I don't know which
number it is, since I don't know how many sheets there are. Do I have
to count the sheets to find out how many there are, then add the
worksheet and select the next number higher than the count?
Thanks for the help!

Set an object variable to the sheet when you add

Set newSheet = worksheets.Add

then refer to the object thereafter, rather than activesheet or other such

newSheet.Name = "Bob"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"davegb" wrote in message
oups.com...
When I add a new worksheet, how do I select it? I don't know which
number it is, since I don't know how many sheets there are. Do I have
to count the sheets to find out how many there are, then add the
worksheet and select the next number higher than the count?
Thanks for the help!

One method would be to insert an Input box into the code, asking what the new
sheet's name should be.

Title = "New Sheet Name"
msg1 = "Insert the new name."

newsheet = InputBox(msg1, Title)
Sheets.Add
ActiveSheet.Name = newsheet

Good luck,

Jim

"Bob Phillips" wrote:

Set an object variable to the sheet when you add

Set newSheet = worksheets.Add

then refer to the object thereafter, rather than activesheet or other such

newSheet.Name = "Bob"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"davegb" wrote in message
oups.com...
When I add a new worksheet, how do I select it? I don't know which
number it is, since I don't know how many sheets there are. Do I have
to count the sheets to find out how many there are, then add the
worksheet and select the next number higher than the count?
Thanks for the help!

Jim Jackson wrote:
One method would be to insert an Input box into the code, asking what the new
sheet's name should be.

Title = "New Sheet Name"
msg1 = "Insert the new name."

newsheet = InputBox(msg1, Title)
Sheets.Add
ActiveSheet.Name = newsheet

Good luck,

Jim

"Bob Phillips" wrote:

Set an object variable to the sheet when you add

Set newSheet = worksheets.Add

then refer to the object thereafter, rather than activesheet or other such

newSheet.Name = "Bob"

--
HTH

Bob Phillips


Thanks for all your replies. As for those who said a newly added sheet
would be automatically selected, I thought so too. But somehow, when I
had some commands using "Activesheet" following the .Add, the commands
were executed on another sheet in another workbook. I don't know how I
managed to do that!

(replace somewhere in email address with gmail if mailing direct)

"davegb" wrote in message
oups.com...
When I add a new worksheet, how do I select it? I don't know which
number it is, since I don't know how many sheets there are. Do I have
to count the sheets to find out how many there are, then add the
worksheet and select the next number higher than the count?
Thanks for the help!