Help in macro in excel.

  • Thread starter Thread starter SPG
  • Start date Start date
S

SPG

Hi all.
I want to write a macro in an excel sheet wherein I want to copy a few cells
from my source worksheet and paste their hyperlink to another worksheet.
Also, the destination worksheet's cell number where this is pasted is not
fixed. How do I accomplish this?
Please help urgently.
Thanks.
 
SPG,

Running the macro recorder whilst inserting a hyperlink yields this code:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Sheet1!A1", TextToDisplay:="""go to hell"""

Now just substitute the relevant stuff into each parameter. It might look
like

ActiveSheet.Hyperlinks.Add
Anchor:=Workbooks("Book4.xls").Sheets("Sheet1").Range("C2"), Address:="",
SubAddress:= _
"[book3.xls]Sheet1!A2", TextToDisplay:="""go here"""

This puts a link in book4, sheet1, C2, and it links to book3, sheet1, A2,
and the link says "go here."
 
Thanks for the response Earl.
Need a little more help.
Can't I get teh normal link instead of the hyperlink to link data in two
workbooks of my same worksheets?


Earl Kiosterud said:
SPG,

Running the macro recorder whilst inserting a hyperlink yields this code:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Sheet1!A1", TextToDisplay:="""go to hell"""

Now just substitute the relevant stuff into each parameter. It might look
like

ActiveSheet.Hyperlinks.Add
Anchor:=Workbooks("Book4.xls").Sheets("Sheet1").Range("C2"), Address:="",
SubAddress:= _
"[book3.xls]Sheet1!A2", TextToDisplay:="""go here"""

This puts a link in book4, sheet1, C2, and it links to book3, sheet1, A2,
and the link says "go here."
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

SPG said:
Hi all.
I want to write a macro in an excel sheet wherein I want to copy a few cells
from my source worksheet and paste their hyperlink to another worksheet.
Also, the destination worksheet's cell number where this is pasted is not
fixed. How do I accomplish this?
Please help urgently.
Thanks.
Click to expand...
Click to expand...
 
SPG,

Do you want to make links manually? Or do you want a macro to do it?

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

SPG said:
Thanks for the response Earl.
Need a little more help.
Can't I get teh normal link instead of the hyperlink to link data in two
workbooks of my same worksheets?


Earl Kiosterud said:
SPG,

Running the macro recorder whilst inserting a hyperlink yields this code:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Sheet1!A1", TextToDisplay:="""go to hell"""

Now just substitute the relevant stuff into each parameter. It might look
like

ActiveSheet.Hyperlinks.Add
Anchor:=Workbooks("Book4.xls").Sheets("Sheet1").Range("C2"), Address:="",
SubAddress:= _
"[book3.xls]Sheet1!A2", TextToDisplay:="""go here"""

This puts a link in book4, sheet1, C2, and it links to book3, sheet1, A2,
and the link says "go here."
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

SPG said:
Hi all.
I want to write a macro in an excel sheet wherein I want to copy a few cells
from my source worksheet and paste their hyperlink to another worksheet.
Also, the destination worksheet's cell number where this is pasted is not
fixed. How do I accomplish this?
Please help urgently.
Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Hi Earl,
I want a macro to do it.
Please suggest.
Thanks a ton !



Earl Kiosterud said:
SPG,

Do you want to make links manually? Or do you want a macro to do it?

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

SPG said:
Thanks for the response Earl.
Need a little more help.
Can't I get teh normal link instead of the hyperlink to link data in two
workbooks of my same worksheets?
Click to expand...
SubAddress:=
_
"Sheet1!A1", TextToDisplay:="""go to hell"""

Now just substitute the relevant stuff into each parameter. It might look
like

ActiveSheet.Hyperlinks.Add
Anchor:=Workbooks("Book4.xls").Sheets("Sheet1").Range("C2"), Address:="",
SubAddress:= _
"[book3.xls]Sheet1!A2", TextToDisplay:="""go here"""

This puts a link in book4, sheet1, C2, and it links to book3, sheet1, A2,
and the link says "go here."
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Hi all.
I want to write a macro in an excel sheet wherein I want to copy a few
cells
from my source worksheet and paste their hyperlink to another worksheet.
Also, the destination worksheet's cell number where this is pasted
Click to expand...
is
not
fixed. How do I accomplish this?
Please help urgently.
Thanks.
Click to expand...
Click to expand...
Click to expand...
 
SPG,

Paste this into a module:

Sub PutLink()
ActiveSheet.Paste Link:=True
End Sub

Now select the cell to which another cell is to be linked, and Copy. Then
select the cell to be linked to it, and run the macro. You may want to
assign the macro to a keyboard shortcut, like Ctrl-Shift-L.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

SPG said:
Hi Earl,
I want a macro to do it.
Please suggest.
Thanks a ton !



Earl Kiosterud said:
SPG,

Do you want to make links manually? Or do you want a macro to do it?

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

SPG said:
Thanks for the response Earl.
Need a little more help.
Can't I get teh normal link instead of the hyperlink to link data in two
workbooks of my same worksheets?


SPG,

Running the macro recorder whilst inserting a hyperlink yields this code:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
Click to expand...
SubAddress:=
_
"Sheet1!A1", TextToDisplay:="""go to hell"""

Now just substitute the relevant stuff into each parameter. It
Click to expand...
might
look
like

ActiveSheet.Hyperlinks.Add
Anchor:=Workbooks("Book4.xls").Sheets("Sheet1").Range("C2"), Address:="",
SubAddress:= _
"[book3.xls]Sheet1!A2", TextToDisplay:="""go here"""

This puts a link in book4, sheet1, C2, and it links to book3,
Click to expand...
sheet1,
A2,
and the link says "go here."
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Hi all.
I want to write a macro in an excel sheet wherein I want to copy a few
cells
from my source worksheet and paste their hyperlink to another worksheet.
Also, the destination worksheet's cell number where this is pasted is
not
fixed. How do I accomplish this?
Please help urgently.
Thanks.
Click to expand...
Click to expand...
Click to expand...
 
SPG,

I forgot to mention. The macro doesn't have to be in the workbook with
either the linked cell or the target cell. It just has to be in an open
workbook. The linked cell can be in a different sheet from the target cell,
and even in a different workbook.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Earl Kiosterud said:
SPG,

Paste this into a module:

Sub PutLink()
ActiveSheet.Paste Link:=True
End Sub

Now select the cell to which another cell is to be linked, and Copy. Then
select the cell to be linked to it, and run the macro. You may want to
assign the macro to a keyboard shortcut, like Ctrl-Shift-L.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

SPG said:
Hi Earl,
I want a macro to do it.
Please suggest.
Thanks a ton !



Earl Kiosterud said:
SPG,

Do you want to make links manually? Or do you want a macro to do it?

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Thanks for the response Earl.
Need a little more help.
Can't I get teh normal link instead of the hyperlink to link data in two
workbooks of my same worksheets?


SPG,

Running the macro recorder whilst inserting a hyperlink yields this
code:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=
_
"Sheet1!A1", TextToDisplay:="""go to hell"""

Now just substitute the relevant stuff into each parameter. It might
look
like

ActiveSheet.Hyperlinks.Add
Anchor:=Workbooks("Book4.xls").Sheets("Sheet1").Range("C2"),
Address:="",
SubAddress:= _
"[book3.xls]Sheet1!A2", TextToDisplay:="""go here"""

This puts a link in book4, sheet1, C2, and it links to book3, sheet1,
A2,
and the link says "go here."
Click to expand...
a
few
cells
from my source worksheet and paste their hyperlink to another
worksheet.
Also, the destination worksheet's cell number where this is
Click to expand...
pasted
is
not
fixed. How do I accomplish this?
Please help urgently.
Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Thanks a ton Earl :-)


Earl Kiosterud said:
SPG,

I forgot to mention. The macro doesn't have to be in the workbook with
either the linked cell or the target cell. It just has to be in an open
workbook. The linked cell can be in a different sheet from the target cell,
and even in a different workbook.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Earl Kiosterud said:
SPG,

Paste this into a module:

Sub PutLink()
ActiveSheet.Paste Link:=True
End Sub

Now select the cell to which another cell is to be linked, and Copy. Then
select the cell to be linked to it, and run the macro. You may want to
assign the macro to a keyboard shortcut, like Ctrl-Shift-L.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

SPG said:
Hi Earl,
I want a macro to do it.
Please suggest.
Thanks a ton !



SPG,

Do you want to make links manually? Or do you want a macro to do it?

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Thanks for the response Earl.
Need a little more help.
Can't I get teh normal link instead of the hyperlink to link data
Click to expand...
in
two
workbooks of my same worksheets?


SPG,

Running the macro recorder whilst inserting a hyperlink yields this
code:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:=
_
"Sheet1!A1", TextToDisplay:="""go to hell"""

Now just substitute the relevant stuff into each parameter. It might
look
like

ActiveSheet.Hyperlinks.Add
Anchor:=Workbooks("Book4.xls").Sheets("Sheet1").Range("C2"),
Address:="",
SubAddress:= _
"[book3.xls]Sheet1!A2", TextToDisplay:="""go here"""

This puts a link in book4, sheet1, C2, and it links to book3, sheet1,
A2,
and the link says "go here."
Click to expand...
Click to expand...
copy
a
Click to expand...
Click to expand...
 
Back
Top