Macro or formula
 

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards

Macro or formula
 

Put this event macro in the worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A3")) Is Nothing Then
Else
Application.Goto reference:=Sheets("Sheet3").Range("A1")
End If
End Sub
--
Gary''s Student - gsnu200823


"santaviga" wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards

Macro or formula
 

On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards


Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Åke

Macro or formula
 

Hi,

I have put this in sheet 3 code area but I cant get it to work when I select
cell A3 on first sheet, first hsheet is called Index of Stock and all other
sheets are only numbered 1,2,3,4,5,6,7,8,9,10 and so on till 150

Any idea what i'm doing wrong?

Regards

"Gary''s Student" wrote:

Put this event macro in the worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A3")) Is Nothing Then
Else
Application.Goto reference:=Sheets("Sheet3").Range("A1")
End If
End Sub
--
Gary''s Student - gsnu200823


"santaviga" wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards

Macro or formula
 

Try putting the code in Sheet "Index", not in sheet "3"
And replace "Sheet3" with just "3" if that is the name of the sheet to
go to.

Hope this helps / Lars-Åke

On Mon, 5 Jan 2009 14:32:01 -0800, santaviga
wrote:

Hi,

I have put this in sheet 3 code area but I cant get it to work when I select
cell A3 on first sheet, first hsheet is called Index of Stock and all other
sheets are only numbered 1,2,3,4,5,6,7,8,9,10 and so on till 150

Any idea what i'm doing wrong?

Regards

"Gary''s Student" wrote:

Put this event macro in the worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A3")) Is Nothing Then
Else
Application.Goto reference:=Sheets("Sheet3").Range("A1")
End If
End Sub
--
Gary''s Student - gsnu200823


"santaviga" wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards

Macro or formula
 

On Mon, 05 Jan 2009 22:29:10 GMT, Lars-Åke Aspelin
wrote:

On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards


Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Åke

Change this to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("3").Activate
End Sub

and remember to put the code in the sheet where you have the "A3"
cell.

Lars-Åke

Macro or formula
 

Got it working thanks, is there a quick way to do this through 150 cells to
150 sheets?

Regards

"Gary''s Student" wrote:

Put this event macro in the worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A3")) Is Nothing Then
Else
Application.Goto reference:=Sheets("Sheet3").Range("A1")
End If
End Sub
--
Gary''s Student - gsnu200823


"santaviga" wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards

Macro or formula
 

Working thanks, is there a way so when I click on a cell in a column A it
will automatically input todays date?

Regards

"Lars-Ã…ke Aspelin" wrote:

On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards


Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Ã…ke

Macro or formula
 

Got it working thanks, is there a quick way to do this through 150 cells to
150 sheets?


"Lars-Ã…ke Aspelin" wrote:

On Mon, 05 Jan 2009 22:29:10 GMT, Lars-Ã…ke Aspelin
wrote:

On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards


Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Ã…ke

Change this to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("3").Activate
End Sub

and remember to put the code in the sheet where you have the "A3"
cell.

Lars-Ã…ke

Macro or formula
 

Try this if you mean "any cell in column A"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then Target.Value
= Date
End Sub

Replace A:A with a specific cell if that is what you mean by "a cell
in column A"

Hope this helps / Lars-Åke

On Mon, 5 Jan 2009 14:51:01 -0800, santaviga
wrote:

Working thanks, is there a way so when I click on a cell in a column A it
will automatically input todays date?

Regards

"Lars-Åke Aspelin" wrote:

On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards


Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Åke

Macro or formula
 

Sorry, but I don't understand what you mean with "do this through 150
cells to 150 sheets". Could you please explain.

Lars-Åke

On Mon, 5 Jan 2009 15:06:00 -0800, santaviga
wrote:

Got it working thanks, is there a quick way to do this through 150 cells to
150 sheets?


"Lars-Åke Aspelin" wrote:

On Mon, 05 Jan 2009 22:29:10 GMT, Lars-Åke Aspelin
wrote:

On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards


Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Åke

Change this to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("3").Activate
End Sub

and remember to put the code in the sheet where you have the "A3"
cell.

Lars-Åke

Macro or formula
 

Many thanks, works a treat.

Thankyou.



"Lars-Ã…ke Aspelin" wrote:

Try this if you mean "any cell in column A"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then Target.Value
= Date
End Sub

Replace A:A with a specific cell if that is what you mean by "a cell
in column A"

Hope this helps / Lars-Ã…ke

On Mon, 5 Jan 2009 14:51:01 -0800, santaviga
wrote:

Working thanks, is there a way so when I click on a cell in a column A it
will automatically input todays date?

Regards

"Lars-Ã…ke Aspelin" wrote:

On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards


Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Ã…ke

Macro or formula
 

Sorry, what I mean is that I have cells A1:A50 with products in them and I
need each of these cells when clicked on to relate to a sheet number 1
through 50, so if I click on cell A46 this will take me to Sheet 46 and so
on, just wondering what the code would be for this.

Regards

"Lars-Ã…ke Aspelin" wrote:

Try this if you mean "any cell in column A"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then Target.Value
= Date
End Sub

Replace A:A with a specific cell if that is what you mean by "a cell
in column A"

Hope this helps / Lars-Ã…ke

On Mon, 5 Jan 2009 14:51:01 -0800, santaviga
wrote:

Working thanks, is there a way so when I click on a cell in a column A it
will automatically input todays date?

Regards

"Lars-Ã…ke Aspelin" wrote:

On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards


Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Ã…ke

Macro or formula
 

You explained in another branch that you wanted sheet 3 to be
activated when you select cell A3 on the first sheet, sheet 4 to
activated when you select cell A4 and so on until cell A50.

Try this code (where I have used 150 rather than 50 as this is what
you state in this branch)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
If Not Intersect(Target, Range("A3:A150")) Is Nothing Then
On Error Resume Next
Set ws = Worksheets(Target.Row)
If ws Is Nothing Then
MsgBox "Sorry, but there is no worksheet named " & Target.Row
Else
ws.Activate
End If
End If
End Sub

Hope this helps / Lars-Åke

On Mon, 05 Jan 2009 23:15:27 GMT, Lars-Åke Aspelin
wrote:

Sorry, but I don't understand what you mean with "do this through 150
cells to 150 sheets". Could you please explain.

Lars-Åke

On Mon, 5 Jan 2009 15:06:00 -0800, santaviga
wrote:

Got it working thanks, is there a quick way to do this through 150 cells to
150 sheets?


"Lars-Åke Aspelin" wrote:

On Mon, 05 Jan 2009 22:29:10 GMT, Lars-Åke Aspelin
wrote:

On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards


Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Åke

Change this to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("3").Activate
End Sub

and remember to put the code in the sheet where you have the "A3"
cell.

Lars-Åke

Macro or formula
 

I have given an answer in the branch where you originally asked for
this. In this branch we discuss how to insert todays date.

Lars-Åke

On Tue, 6 Jan 2009 05:38:01 -0800, santaviga
wrote:

Sorry, what I mean is that I have cells A1:A50 with products in them and I
need each of these cells when clicked on to relate to a sheet number 1
through 50, so if I click on cell A46 this will take me to Sheet 46 and so
on, just wondering what the code would be for this.

Regards

"Lars-Åke Aspelin" wrote:

Try this if you mean "any cell in column A"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then Target.Value
= Date
End Sub

Replace A:A with a specific cell if that is what you mean by "a cell
in column A"

Hope this helps / Lars-Åke

On Mon, 5 Jan 2009 14:51:01 -0800, santaviga
wrote:

Working thanks, is there a way so when I click on a cell in a column A it
will automatically input todays date?

Regards

"Lars-Åke Aspelin" wrote:

On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards


Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Åke

Macro or formula
 

Is thre a way to have multiple cells and sheets working like I have cells
A1:A50 with products in them and I
need each of these cells when clicked on to relate to a sheet number 1
through 50, so if I click on cell A46 this will take me to Sheet 46 and so
on, just wondering what the code would be for this.

Regards



"Lars-Ã…ke Aspelin" wrote:

On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards


Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Ã…ke

Macro or formula
 

See my answer in another branch of this thread.

Lars-Åke

On Tue, 6 Jan 2009 07:00:12 -0800, santaviga
wrote:

Is thre a way to have multiple cells and sheets working like I have cells
A1:A50 with products in them and I
need each of these cells when clicked on to relate to a sheet number 1
through 50, so if I click on cell A46 this will take me to Sheet 46 and so
on, just wondering what the code would be for this.

Regards



"Lars-Åke Aspelin" wrote:

On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards


Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Åke

Macro or formula
 

Hi, sorry for not seeing your previous post, found it now and trying, it
works to an extent, when I click on a cell say A1 it opens Sheet 2 and not
sheet 1 as supposed to and son on, it always opens the sheet number above.

Any ideas?

Thanks

"Lars-Ã…ke Aspelin" wrote:

See my answer in another branch of this thread.

Lars-Ã…ke

On Tue, 6 Jan 2009 07:00:12 -0800, santaviga
wrote:

Is thre a way to have multiple cells and sheets working like I have cells
A1:A50 with products in them and I
need each of these cells when clicked on to relate to a sheet number 1
through 50, so if I click on cell A46 this will take me to Sheet 46 and so
on, just wondering what the code would be for this.

Regards



"Lars-Ã…ke Aspelin" wrote:

On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards


Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Ã…ke

Macro or formula
 

Sorry it is opening sheet number below

"santaviga" wrote:

Hi, sorry for not seeing your previous post, found it now and trying, it
works to an extent, when I click on a cell say A1 it opens Sheet 2 and not
sheet 1 as supposed to and son on, it always opens the sheet number above.

Any ideas?

Thanks

"Lars-Ã…ke Aspelin" wrote:

See my answer in another branch of this thread.

Lars-Ã…ke

On Tue, 6 Jan 2009 07:00:12 -0800, santaviga
wrote:

Is thre a way to have multiple cells and sheets working like I have cells
A1:A50 with products in them and I
need each of these cells when clicked on to relate to a sheet number 1
through 50, so if I click on cell A46 this will take me to Sheet 46 and so
on, just wondering what the code would be for this.

Regards



"Lars-Ã…ke Aspelin" wrote:

On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards


Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Ã…ke

Macro or formula
 

Hi, here is my revised code I am using. It is opening sheet number below cell
number.

Many thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
If Not Intersect(Target, Range("B3:B52")) Is Nothing Then
On Error Resume Next
Set ws = Worksheets(Target.Row)
If ws Is Nothing Then
MsgBox "Sorry, but there is no worksheet named " & Target.Row
Else
ws.Activate
End If
End If
End Sub


"Lars-Ã…ke Aspelin" wrote:

See my answer in another branch of this thread.

Lars-Ã…ke

On Tue, 6 Jan 2009 07:00:12 -0800, santaviga
wrote:

Is thre a way to have multiple cells and sheets working like I have cells
A1:A50 with products in them and I
need each of these cells when clicked on to relate to a sheet number 1
through 50, so if I click on cell A46 this will take me to Sheet 46 and so
on, just wondering what the code would be for this.

Regards



"Lars-Ã…ke Aspelin" wrote:

On Mon, 5 Jan 2009 14:08:03 -0800, santaviga
wrote:

Hi,

I am looking for a formula or macro so that when an active cell is selected
lets say cell A3 is selected excel will automatically go to a sheet number
specified say Sheet 3.

Any help would be much appreciated.

Regards


Try this macro in the worksheet where your cell A3 is located

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Worksheets("Sheet3").Activate
End Sub

Hope this helps / Lars-Ã…ke