Advanced Excel--Want to click on Cell & Move to Other Tab

[Melanie G.]

Hello Excel Users-

I am working on a giant monster with 18 tabs on it. I want one of my cells
on one of my tabs to just take me directly to another tab. The point of this
is to stop duplicating information; I don't want to cram the same information
into two places when it just doesn't need to be done. I would rather just
have a special cell take you directly to another tab when you click on that
cell.

I know how to insert formulas and values from Tab A into a cell in Tab B,
but I am struggling to get the entirely of Tab A into Cell 1 in Tab B.

Am I creating a link here? Do I need to insert another workbook somehow? Is
this possible? Does it make sense? Can you help me?

Thank you,

Melanie

 

[Susan]

Melanie -
it sounds like you are asking 2 different things. if you want to
physically travel from one sheet to another with the click (or maybe
double-click?) of a certain cell, i can do that.
but then you talk about getting all the information in tab A into
sheet B's first cell. that's a little more difficult!

so i'm going to address the movement one, first. pick a cell, i've
picked A1 just to be easy, in each sheet. leave that cell blank in
all worksheets

i'm not a guru. i've completed this lovely little macro, which works
wonderfully, in whatever worksheet module you put it in. but the only
way i can think of getting it to work for you is for you to enter it
18 times(!!!) in each spreadsheet module!

i will post the macro just in case nobody else responds. sorry.
'========Start of Macro==============
Option Explicit

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Dim wb As Workbook
Dim x As Long
Dim i As Long
Dim Wks As Worksheet
Dim r As Range

If Target.Address = "$A$1" Then

Set wb = ActiveWorkbook

x = wb.Sheets.Count

Set Wks = ActiveSheet
i = Wks.Index
If i = x Then
i = 1
Else
i = i + 1
End If
Set Wks = wb.Worksheets(i)
Wks.Activate
End If

End Sub
'===========End of Macro==============
hope it helps.
susan

 

[Gord Dibben]

Susan

You don't have to replicate your event code 18 times.

Just place it once in Thisworkbook module as this event type.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal _
Sh As Object, ByVal Target As Range, Cancel As Boolean)


Gord Dibben MS Excel MVP

 

[Susan]

Gord -
thank you! i didn't know that (obviously).

susan