Hi Team,

Can someone help me with a code.

I have one sheet with the name as Go_To. In this sheet I have dates mentioned from 1st Jan to 31st Dec.

I have one more sheet with the name as Status_Report. The sheet has been designed day wise.

What I want is if I click on a specific date on Go_To sheet it shld take me to specific cell on the sheet named as Status_Report.

I can do it using Hyperlink but it wld be a too leanthy process. Can someone help me with a code for the same.

Awaiting for a helping hand.

Rgds

Akash Maheshwari

Hi Akash,

Am Fri, 18 Dec 2015 10:47:53 -0800 (PST) schrieb Akash:

I have one sheet with the name as Go_To. In this sheet I have dates mentioned from 1st Jan to 31st Dec.

I have one more sheet with the name as Status_Report. The sheet has been designed day wise.

What I want is if I click on a specific date on Go_To sheet it shld take me to specific cell on the sheet named as Status_Report.

I can do it using Hyperlink but it wld be a too leanthy process. Can someone help me with a code for the same.

not enough informations.
In which range on Go_To and in which range on Status_Report are the
dates?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi,

In Go_To sheet? I have columns for every month from Jan to Dec.

JAN 1st is in cell A2
JAN 31st is in cell A32

FEB 1st is in cell B2
FEB 29th is in cell B30

Suppose if I click on Cell A2 i wld like to go on a date mentioned on a specific cell in the sheet named as Status_Report.

Hope this clerifies ur confussion.

Rgds

Akash Maheshwari

Hi Akash,

Am Fri, 18 Dec 2015 11:00:41 -0800 (PST) schrieb Akash:

JAN 1st is in cell A2
JAN 31st is in cell A32

FEB 1st is in cell B2
FEB 29th is in cell B30

Suppose if I click on Cell A2 i wld like to go on a date mentioned on a specific cell in the sheet named as Status_Report.

right click on sheet tab of Go_To = Show code and insert following
code:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
If Intersect(Target, Range("A2:L32")) Is Nothing _
Then Exit Sub

Dim c As Range

Cancel = True
Set c = Sheets("Status_report").UsedRange.Find(Target, LookIn:=xlValues)
If Not c Is Nothing Then Application.Goto c
End Sub

With right click on a date you will come to sheet "Status_Reoprt".


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hello Mr. Claus,

The code didnt worked....

I am trying to explain you in another way.

I have two sheets:
One Sheet name "Go_To".
Second Sheet name is "Status Report".

In the Sheet Go_To I have one date as 12/19/2015 in Cell A2
In the Sheet Status_Report I have a section with is related to 12/19/2015 in the cell B25.

I can do this through hyperlink but I dont want to do it in that way as its a very long process. Instead of this I want to use a code which can link the cell in Go_To sheet (Cell A2) with Status_Report Sheet (Cell B25). I would replicate code for other dates tooo.

Hope I am able to to make you understand the requirement.

Awaiting for your response.

Best Regards

Akash Maheshwari

Hi Akash,

Am Fri, 18 Dec 2015 18:12:25 -0800 (PST) schrieb Akash:

The code didnt worked....

then you are doing something wrong. The code is tested and if you right
click on a date in Go_To and this date exists in "Status_Report" it will
be selected in "Status_Report".


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus,

I dont want to select a Cell in the sheet named as "Status_Report".

It should work like Hyperlink. If I click on a date mentioned in the cell on the sheet named as Go_To then it should take me to a specific cell on the sheet named as Status_Report".

I dont want to use hyperlink option as its very tedious. Instead of it If I can get a code it wld be very handy.

Hope I am able to make you understand.

Best Regards

Akash Maheshwari



On Saturday, December 19, 2015 at 1:40:57 PM UTC+5:30, Claus Busch wrote:
Hi Akash,

Am Fri, 18 Dec 2015 18:12:25 -0800 (PST) schrieb Akash:

The code didnt worked....

then you are doing something wrong. The code is tested and if you right
click on a date in Go_To and this date exists in "Status_Report" it will
be selected in "Status_Report".


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Akash,

Am Sun, 20 Dec 2015 04:48:15 -0800 (PST) schrieb Akash:

It should work like Hyperlink. If I click on a date mentioned in the cell on the sheet named as Go_To then it should take me to a specific cell on the sheet named as Status_Report".

look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Status_Report" and download the file because macros are disabled in
OneDrive.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Akash,

Am Sun, 20 Dec 2015 04:48:15 -0800 (PST) schrieb Akash:

It should work like Hyperlink. If I click on a date mentioned in the
cell on the sheet named as Go_To then it should take me to a
specific cell on the sheet named as Status_Report".

look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Status_Report" and download the file because macros are disabled
in OneDrive.


Regards
Claus B.

Claus,
Perhaps the issue is ScrollRow, depending on where the GoTo target is?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Sun, 20 Dec 2015 14:13:48 -0500 schrieb GS:

Perhaps the issue is ScrollRow, depending on where the GoTo target is?

I hope for a better explanation of his problem.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hello Mr. Clause,

The code is working when I have only one date in the sheet named as Status_Report. When I have same date twice in the sheet named as Status_Report its not working.

Can i have code which states that if a user clicks on cell A1 of the sheet named as Go_To, then system should take him to the cell No C5 in sheet named as Status_Report.

I tried to make a macro but a macro has been created.

Sub Macro3()
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Status_Report!C5"
End Sub

I dont want a macro. I want a simple code to solve this issue.

Hope I am able to understand you my problem.

Best Regards

Akash Maheshwari

Put this code in Go_to sheet.in case your status_report sheet contains duplicates it will take you to first value which comes in the Sheet, you need to double click on the cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("A2:A50000"), Target) Is Nothing Then
Set d = Sheets("Status_Report").Range("A1:A50000").Find(Wh at:=Target, LookIn:=xlValues, lookat:=xlWhole)
' MsgBox d.Address
Sheets("Status_report").Activate
Sheets("Status_report").Range(d.Address).Select
End If
End Sub

Regards,
Mandeep baluja