Create a macor that creates Hyperlink in one cell that links to a row of cells

[bigsistar12]

I will be importing data in Column A:AF.

This is what I am trying to do:
Each time new formation is entered in a row ex A3:Af3
A3 will be a hyperlink that references all cells A3:AF3

I found this but it creates a hyperlink to each cell in row 3 intead of creating one link to range A3:AF3
Public Sub Convert_To_Hyperlinks()
Dim Cell As Range
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
If Cell <> "" Then
ActiveSheet.Hyperlinks.Add Cell, Cell.Value
End If
Next
End Sub


This Action creates hyperlink in A3 which is perfect but the hyperlink does not link to anything.

ActiveCell.Hyperlinks.Add ActiveCell, ActiveCell.Value

Can anyone help me?

I am new to Macros and still learning the codes.

 

[p45cal]

try:

    ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="'" & ActiveSheet.Name & "'!" & ActiveCell.Resize(, 32).Address, TextToDisplay:=ActiveCell.Value

 

[bigsistar12]

Hi p45cal Thanks! its giving me a run time error. What am I doing wrong?

here is what I am doing:

Sub NewPatient()
Sheets("Insurance1").Select
Rows("3:3").Select
Selection.Insert Shift:=xlDown

Application.ScreenUpdating = False
Worksheets("insurance1").Range("a3").Value = Worksheets("pricingcalculator").Range("B1").Value
Worksheets("insurance1").Range("b3").Value = Worksheets("pricingcalculator").Range("g1").Value
Worksheets("insurance1").Range("c3").Value = Worksheets("pricingcalculator").Range("b2").Value
Worksheets("insurance1").Range("d3").Value = Worksheets("pricingcalculator").Range("g2").Value
Worksheets("insurance1").Range("e3").Value = Worksheets("pricingcalculator").Range("b3").Value
Worksheets("insurance1").Range("f3").Value = Worksheets("pricingcalculator").Range("g3").Value
Worksheets("insurance1").Range("g3").Value = Worksheets("pricingcalculator").Range("b4").Value
Worksheets("insurance1").Range("h3").Value = Worksheets("pricingcalculator").Range("f4").Value
Worksheets("insurance1").Range("i3").Value = Worksheets("pricingcalculator").Range("h4").Value
Worksheets("insurance1").Range("j3").Value = Worksheets("pricingcalculator").Range("b5").Value
Worksheets("insurance1").Range("k3").Value = Worksheets("pricingcalculator").Range("f5").Value
Worksheets("insurance1").Range("l3").Value = Worksheets("pricingcalculator").Range("h5").Value
Worksheets("insurance1").Range("m3").Value = Worksheets("pricingcalculator").Range("b6").Value
Worksheets("insurance1").Range("n3").Value = Worksheets("pricingcalculator").Range("f6").Value
Worksheets("insurance1").Range("o3").Value = Worksheets("pricingcalculator").Range("h6").Value
Worksheets("insurance1").Range("p3").Value = Worksheets("pricingcalculator").Range("b8").Value
Worksheets("insurance1").Range("q3").Value = Worksheets("pricingcalculator").Range("f8").Value
Worksheets("insurance1").Range("r3").Value = Worksheets("pricingcalculator").Range("h8").Value
Worksheets("insurance1").Range("s3").Value = Worksheets("pricingcalculator").Range("b9").Value
Worksheets("insurance1").Range("t3").Value = Worksheets("pricingcalculator").Range("f9").Value
Worksheets("insurance1").Range("u3").Value = Worksheets("pricingcalculator").Range("h9").Value
Worksheets("insurance1").Range("v3").Value = Worksheets("pricingcalculator").Range("b10").Value
Worksheets("insurance1").Range("w3").Value = Worksheets("pricingcalculator").Range("f10").Value
Worksheets("insurance1").Range("x3").Value = Worksheets("pricingcalculator").Range("h10").Value
Worksheets("insurance1").Range("y3").Value = Worksheets("pricingcalculator").Range("b11").Value
Worksheets("insurance1").Range("z3").Value = Worksheets("pricingcalculator").Range("f11").Value
Worksheets("insurance1").Range("aa3").Value = Worksheets("pricingcalculator").Range("h11").Value
Worksheets("insurance1").Range("ab3").Value = Worksheets("pricingcalculator").Range("b12").Value
Worksheets("insurance1").Range("ac3").Value = Worksheets("pricingcalculator").Range("f12").Value
Worksheets("insurance1").Range("ad3").Value = Worksheets("pricingcalculator").Range("h12").Value
Worksheets("insurance1").Range("ae3").Value = Worksheets("pricingcalculator").Range("B13").Value
Worksheets("insurance1").Range("af3").Value = Worksheets("pricingcalculator").Range("B14").Value
Application.ScreenUpdating = True

ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="'" & ActiveSheet.Name & "'!" & ActiveCell.Resize(, 32).Address, TextToDisplay:=ActiveCell.Value

ActiveWorkbook.Save

End Sub

 

[p45cal]

What's the full text of the runtime error?

try:

Sub NewPatient()
Sheets("Insurance1").Rows("3:3").Insert Shift:=xlDown
i = 0
Set myAnchor = Worksheets("insurance1").Range("a3")
Application.ScreenUpdating = False
For Each cll In Worksheets("pricingcalculator").Range("B1,G1,B2,G2,B3,G3,B4,F4,H4,B5,F5,H5,B6,F6,H6,B8,F8,H8,B9,F9,H9,B10,F10,H10,B11,F11,H11,B12,F12,H12,B13,B14").Cells
  myAnchor.Offset(, i).Value = cll.Value
  i = i + 1
Next cll
myAnchor.Parent.Hyperlinks.Add Anchor:=myAnchor, Address:="", SubAddress:=myAnchor.Resize(, 32).Address(False, False, ReferenceStyle:=xlR1C1, relativeto:=myAnchor), TextToDisplay:=CStr(myAnchor.Value)
Application.ScreenUpdating = True
End Sub

 

[bigsistar12]

run time error 5

invalid procedure call or argument

 

[bigsistar12]

Hi p45cal

this works wonderfully! thank you!

Now is there a way where I can make this link work backwards. where if i click it the information goes is imported in the original form the way it was exported?

Basically its a form that is filled out, then it is saved on a separate worksheet like a database, but when the user wants to reuse the information from the data base they can click on the link for the database row and it will repopulate the form.

New thread: http://www.mrexcel.com/forum/showthread.php?t=538314

 

[p45cal]

Is A3 a number?
Did the code in msg#4 solve it?

 

[bigsistar12]

yes msg#4 solved it works great! You are my hero!

-michelle