VB command to direct person to specific worksheet

G

Guest

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


Andy
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Upvote 0
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
 
Upvote 0
Good Advice Nate with my example if the sheet
name was changed Andy would be up the creek!

Jim
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top