go to sheet number

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 24 Jul 2006 17:46 GMT

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

Reply to this Message

Chip Pearson - 24 Jul 2006 17:53 GMT

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

Sheets(10).Activate

Signature

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

> Errors are given in sheet numbers but the sheets have names not
> numbers.
[quoted text clipped - 4 lines]
> Thanks
> John

Reply to this Message

John - 24 Jul 2006 18:20 GMT

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

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

Reply to this Message

Chip Pearson - 24 Jul 2006 18:26 GMT

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

Signature

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

>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.
[quoted text clipped - 5 lines]
>>
>> Sheets(10).Activate

Reply to this Message

John - 24 Jul 2006 18:57 GMT

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

> There is no way to go to a sheet based on its index number
> without using VBA or manually counting over that many sheets.
[quoted text clipped - 12 lines]
> End If
> End Sub

Reply to this Message

SteveW - 24 Jul 2006 19:29 GMT

> 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
[quoted text clipped - 3 lines]
> 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
>
[quoted text clipped - 10 lines]
>> End If
>> End Sub

Signature

Steve (3)

Reply to this Message

Rate this thread: