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

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



On Oct 20, 1:36*pm, Melanie G.
wrote:
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

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

On Tue, 20 Oct 2009 11:17:02 -0700 (PDT), Susan wrote:

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



On Oct 20, 1:36*pm, Melanie G.
wrote:
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

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



On Oct 20, 5:49*pm, Gord Dibben <gorddibbATshawDOTca wrote:
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



On Tue, 20 Oct 2009 11:17:02 -0700 (PDT), Susan wrote:
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

On Oct 20, 1:36*pm, Melanie G.
wrote:
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- Hide quoted text -

- Show quoted text -