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

B

bigsistar12

New Member
Joined
Mar 10, 2011
Messages
18
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
try:
Code: 
    ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="'" & ActiveSheet.Name & "'!" & ActiveCell.Resize(, 32).Address, TextToDisplay:=ActiveCell.Value
 
Upvote 0
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
 
Upvote 0
What's the full text of the runtime error?

try:
Code: 
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
2
Replies
10
Views
162
Alex Blakenburg
A
E
  • Locked
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
Replies
2
Views
95
Fluff
Fluff
M
  • Solved
Hyperlink on VBA
Replies
2
Views
78
MSqueries
M
A
  • Solved
VBA to list all links (external links AND hyperlinks) together with the cells containing the links
Replies
3
Views
2K
pbornemeier
pbornemeier
C
  • Solved
VBA code to create a word document, link it to a cell and save the document using the cell value
Replies
3
Views
665
cmannp
C

Forum statistics

Threads
1,224,536
Messages
6,179,402
Members
452,909
Latest member
VickiS

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top