Hyperlink for first column of each row running a macro ?

[gopipuli]

Hi

First up all thank you very much for this wonderful forum. I am not good in vba. so I need help from you wonderful people here. I am looking for something like this

I have an excel file which contain two sheets. The first sheet contain my data in rows, the second sheet is a template which I need to open by clicking first column which is a hyperlink to open the respective row data with the second sheet. Like we do in html clicking link and opening details. I manage to gather information from web that this could be done by creating a vba code that copy data from one sheet to other. But I dont know how to link this macro to each row ? Please give me a start on this topic.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screens shots of both sheets. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples form your data.

 

[gopipuli]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screens shots of both sheets. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples form your data.

I am attaching the screen shots , my data in sheet1

my template sheet2

Name column in sheet1 must be a hyperlink which direct me to sheet2 to view the respective data in selected row. How can I achieve this ? Please help.

 

[gopipuli]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screens shots of both sheets. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples form your data.

so when I click reach row hyperlink in the first column should take the respective data and it is viewed with sheet2.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your data sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. I have used a double click macro which will require you to double click in column A of the data sheet instead of just clicking. This will allow you to click in column A to add or modify data without triggering the macro.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Target.Resize(, 4).Copy
    Sheets("Template").Range("C3").PasteSpecial Transpose:=True
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Keep in mind that the macro is written based on the screenshots that you posted.

 

[gopipuli]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your data sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. I have used a double click macro which will require you to double click in column A of the data sheet instead of just clicking. This will allow you to click in column A to add or modify data without triggering the macro.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Target.Resize(, 4).Copy
    Sheets("Template").Range("C3").PasteSpecial Transpose:=True
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Keep in mind that the macro is written based on the screenshots that you posted.

Thank you very much for this excellent help. Now sorry to ask one more doubt on this. If I want to place value from data sheet to different cells range what should I modify on the above code. The above code works perfect.

 

[mumps]

"If I want to place value from data sheet to different cells range"
Which values from the data sheet and what different cell range? Please describe in detail.

 

[gopipuli]

"If I want to place value from data sheet to different cells range"
Which values from the data sheet and what different cell range? Please describe in detail.

Suppose My data Format is like this

and my template is

this

how can I change the given code to place data as shown in above ? Thank you once again.

 

[mumps]

Will there always be 5 addresses? I noticed that you didn't include cell E3 (Line1) in the Template sheet. Do you always want to exclude the Address1 data?

 

[gopipuli]

Will there always be 5 addresses? I noticed that you didn't include cell E3 (Line1) in the Template sheet. Do you always want to exclude the Address1 data?

Sorry That was a mistake here it is

Any of the address columns may or may not contains data. Thank you mumps for such a fine response.