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

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!

[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


"davegb" <[email protected]> schreef in bericht
news:[email protected]...
> 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]

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!

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

"davegb" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >
>
>
>

[davegb]

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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!
> > >
> >
> >
> >