VB command to direct person to specific worksheet

What is the command in a VB macro to get a spreadsheet opened on a specific cell in a specific sheet each time?


Andy

[James]

If i understand your question correctly:

Put this in a module:

Private Sub"your macros name"()
Worksheets("your worksheets name").Select
Range("your cell").Select
End Sub

Jim

[NateO]

Sometimes I like referencing a worksheet by name, which the post above handles, and sometimes by its physical location in the workbook. E.g., if you always want to go to the second worksheet (no matter what the name) try:

worksheets(2).select

and so forth.

Cheers, Nate

[ This Message was edited by: NateO on 2002-02-27 19:41 ]

[James]

Good Advice Nate with my example if the sheet
name was changed Andy would be up the creek!

Jim

[NateO]

I've seen it happen James, and vice versa, where you always want a sheet no matter where the location...Referencing worksheet #'s does get a little trickier when hidden sheets are involved (i.e., you think you're working with the 2nd sheet and it's really the third), but I'll save that for another post. Have a good one James.

Cheers, Nate

[ This Message was edited by: NateO on 2002-02-27 19:39 ]

[Ivan F Moala]

On 2002-02-27 19:36, NateO wrote:
I've seen it happen James, and vice versa, where you always want a sheet no matter where the location...Referencing worksheet #'s does get a little trickier when hidden sheets are involved (i.e., you think you're working with the 2nd sheet and it's really the third), but I'll save that for another post. Have a good one James.

Cheers, Nate

[ This Message was edited by: NateO on 2002-02-27 19:39 ]

Try using the Sheets Code name for situations
like this....ie instead of Sheet(2) which is the 2nd sheet where as Sheets2.Acivate activates the Sheet code named Sheet2 and not the 2nd sheet.
The Sheets code name you will see in the project explorer to the left of the sheet tab name you cannot change the sheets code name except through code or manually changing while in the VBE.

Have a look @ project explorer you should see
something like

Sheet1(Sheet1) or Sheet1(Tabname)

the name to the left will not change...this
is how you should access the sheet if you want to be bable to change the Tab name without effecting the code.
So...........

Works ALL the time no matter what you change the Tab name to.
Sub test()
Sheet1.Activate
End Sub


Works all the time unless the user changes the sheet tab name.
Sub test2()
Sheets("Sheet1").Activate
End Sub

Works all the time IF you want to Access the 1st sheet ONLY irrespective of the sheet name
Sub test3()
Sheets(1).Activate
End Sub


Ivan

[NateO]

Nice, I'll give this a try:

On 2002-02-27 20:11, Ivan F Moala wrote:

Works ALL the time no matter what you change the Tab name to.
Sub test()
Sheet1.Activate
End Sub

So this works whether you change the name of the sheet or its physical location in the workbook, nice.

I had actually created a routine to shift all hidden worksheets to the last sheets of the file which allowed me to identify their order (based on what you see), but wasn't as hot when one unhides them (didn't necessarily want to rearrange the workbook).

Thanks for the insight. Cheers,

Nate

[ This Message was edited by: NateO on 2002-02-27 20:52 ]