Paste Link

[Lance]

I have a workbook with multiple sheets in it, and then a copy of the multiple
sheets, ie: a1, a2, a3, a4, and b1, b2, b3, b4. b1 is identical to a1 but in
a different language (all columns/rows match exactly). I would like to write
a macro that allows me to select particular cells from a1, then pastes and
links them into their equivalent in b2. Basically, just a shorter way then
selecting a A:1 on a1 copying it, switching to b1, clicking on A:1 and
pasting special link.

Any help would be appreciated, thanks,

Lance

 

[OssieMac]

Hi Lance,

How about Double Cick the cell to copy and past the link. You could use the
following code. Not sure how competent you are with macros so to use the
macro, simply right click on the sheet tab name (of the sheet where you are
copying from) and select View Code to open the VBA editor for the selected
sheet.

Copy the code into the VBA editor and close the editor by clicking on the X
top right of screen.

You will need to edit the sheet names if you have different names to Sheet1
and Sheet2.

You can then Double Click the cell to copy and the link will be placed on
the other sheet.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim strAddress

Set sht1 = Sheets("Sheet1")
Set sht2 = Sheets("Sheet2")

strAddress = Target.Address
Selection.Copy
sht2.Select
ActiveSheet.Range(strAddress).Select
ActiveSheet.Paste Link:=True
sht1.Select

'Cancel the double click so the cell
'does not remain in Edit mode
Cancel = True
Application.CutCopyMode = False

End Sub