go to sheet number

Errors are given in sheet numbers but the sheets have names not numbers.

How do you "go to" a sheet number? I have about 200 in a workbook.

Thanks
John

You can access a sheet by its position number. E.g.,

Sheets(10).Activate


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"John" <[email protected]> wrote in message
news:[email protected]...
> Errors are given in sheet numbers but the sheets have names not
> numbers.
>
> How do you "go to" a sheet number? I have about 200 in a
> workbook.
>
> Thanks
> John

I didn't mean in vb. I'm in the workbook and want to go to sheet #32 but
the sheets only have names on them... no numbers.

Thanks
John

Chip Pearson wrote:
> You can access a sheet by its position number. E.g.,
>
> Sheets(10).Activate
>
>

There is no way to go to a sheet based on its index number
without using VBA or manually counting over that many sheets.

You could use the following macro and assign it to a shortcut
key.

Sub GoToSheetNumber()
Dim N As Long
N = Application.InputBox(prompt:="Enter a sheet number",
Type:=1)
If N < 1 Or N > Sheets.Count Then
MsgBox "Invalid sheet number"
Else
Sheets(N).Select
End If
End Sub




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"John" <[email protected]> wrote in message
news:[email protected]...
>I didn't mean in vb. I'm in the workbook and want to go to sheet
>#32 but the sheets only have names on them... no numbers.
>
> Thanks
> John
>
> Chip Pearson wrote:
>> You can access a sheet by its position number. E.g.,
>>
>> Sheets(10).Activate
>>

There is no way to go to a sheet based on its index number
without using VBA or manually counting over that many sheets.

You could use the following macro and assign it to a shortcut
key.

Sub GoToSheetNumber()
Dim N As Long
N = Application.InputBox(prompt:="Enter a sheet number",
Type:=1)
If N < 1 Or N > Sheets.Count Then
MsgBox "Invalid sheet number"
Else
Sheets(N).Select
End If
End Sub




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"John" <[email protected]> wrote in message
news:[email protected]...
>I didn't mean in vb. I'm in the workbook and want to go to sheet
>#32 but the sheets only have names on them... no numbers.
>
> Thanks
> John
>
> Chip Pearson wrote:
>> You can access a sheet by its position number. E.g.,
>>
>> Sheets(10).Activate
>>

They are listed in the project window in vb thank God and easy to select
and go to. It also lists there all your vb modules but, of course, by
number not name. It's as if the designers purposely made it as obscure
as possible navigating around your workbooks.

I exported all my modules and renamed them and loaded them again so I
could identify them easily. MS promptly renamed them by module numbers.

John

Chip Pearson wrote:

> There is no way to go to a sheet based on its index number
> without using VBA or manually counting over that many sheets.
>
> You could use the following macro and assign it to a shortcut
> key.
>
> Sub GoToSheetNumber()
> Dim N As Long
> N = Application.InputBox(prompt:="Enter a sheet number",
> Type:=1)
> If N < 1 Or N > Sheets.Count Then
> MsgBox "Invalid sheet number"
> Else
> Sheets(N).Select
> End If
> End Sub
>
>
>
>

On Mon, 24 Jul 2006 18:57:16 +0100, John <[email protected]> wrote:

> They are listed in the project window in vb thank God and easy to sele=
ct =

> and go to. It also lists there all your vb modules but, of course, by =
=

> number not name. It's as if the designers purposely made it as obscure=
=

> as possible navigating around your workbooks.
>
> I exported all my modules and renamed them and loaded them again so I =
=

> could identify them easily. MS promptly renamed them by module numbers=
..
>

You can just rename them - F4 - alter properties


> John
>
> Chip Pearson wrote:
>
>> There is no way to go to a sheet based on its index number without =

>> using VBA or manually counting over that many sheets.
>> You could use the following macro and assign it to a shortcut key.
>> Sub GoToSheetNumber()
>> Dim N As Long
>> N =3D Application.InputBox(prompt:=3D"Enter a sheet number", Type=
:=3D1)
>> If N < 1 Or N > Sheets.Count Then
>> MsgBox "Invalid sheet number"
>> Else
>> Sheets(N).Select
>> End If
>> End Sub
>>



-- =

Steve (3)