How to select an added worksheet

[davegb]

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!

 

[Duncan]

if you use

activeworkbook.Sheets.Add

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

Duncan

 

[Gert-Jan]

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

 

[Duncan]

or..

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

then you know the name of the added sheet

 

[Bob Phillips]

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)

 

[Guest]

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

 

[davegb]

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

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!