macro to redirect to another worsheet within the same workbook

[Jack Wood]

I have a workbook consisting of about 20 or more tabs. The 1st sheet is
titled Index. Looking for a macro that when an item is selected on the
index, you are automatically directed to a specified worksheet & then
need a macro on the specified sheet that will automatically send you
back to the index. I cannot use regular hyperlinks because the workbook
that you input your entries on has to be a copy of the original workbook
& when you make a copy of the original workbook & use a regular
hyperlink to another worksheet, it will send you back to the original
workbook.

 

[FSt1]

hi
as an alternative, try right clicking the sheet navigator in the lower left
of the sheet.
you will get a popup with a list of your sheets. click one to go to it.

regards
FSt1

 

[Jack Wood]

Now for the next problem. In one of my worksheets a have typed in it
"SCROLL RIGHT & USE APPROPRIATE INPUT AREA". I have hyperlinked so when
you click on "SCROLL RIGHT & USE APPROPRIATE INPUT AREA" it
automatically scrolls right to the specified cell # so you can start
your input. This will not work in a copy of the original file. How can
I get it to scroll right automatically without it going back to the
original file.

 

[FSt1]

hi
as a suggestion, you might try the worksheet activation event....

Private Sub Worksheet_Activate()
Range("G5").Select 'adjust to suit
MsgBox "SCROLL RIGHT & USE APPROPRIATE INPUT AREA"
'MsgBox "Start input at G5" 'as an alternative instruction.
End Sub

this is worksheet code so right click the sheet tab, click "view code" and
paste there.

regards
FSt1

 

[Jack Wood]

With this code I am have to run the macro twice before it will
automatically scroll over. Also, I have numerous cells in the worksheet
that has the words "Scroll Over & Use the Approriate Input Area. Need
each one of those to recognize were to scroll over.

 

[FSt1]

hi
it's not suppose to scroll over. it selects a specific cell on sheet
activation then displays the message. it works on my pc. i think you might be
hung up on this scroll over thing.

as to the other problem with the multiple scroll overs. there is no cell
click event. you might try dropping a label from the control tool box and
size it to the cell.
then right click the label, click properties and add your caption to the
label....
Scroll Over & Use the Approriate Input Area
then right click the label, click view code. use this
Private Sub Label1_Click()
Range("G5").Select 'adjust to suit
End Sub

G5 is just what i used to test. change that to what you need.

regards
FSt1

 

[Jack Wood]

I understand about right clicking on the Naviagation Pane to see the
list of tabs, but it does not list the tabs in the order that they are
filled out by the Engineer. The index allows me to list the Input
sheets in order & then the Ouput Sheets. Still looking for a macro that
when an item is selected on the Index tab, will be directed
automatically to the sheet.