I want to paste link from one sheet to another

Range("A1:D1").Select
Range("D1").Activate
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste Link:=True

However, the code would make the sheet to switch to Sheet2 from Sheet1. Is there anyway that I could paste the link without switching the sheet?

Thanks.

• excel-vba
• vba
• excel

This will work:

ThisWorkbook.Worksheets("Sheet2").Range("D1").Formula = "=Sheet1!D1"

I guess this is what you are trying?

Sub Sample()
    Dim i As Long
    For i = 1 To 4
        Sheets("Sheet2").Cells(1, i).Formula = "=Sheet1!" & _
                Split(Cells(, i).Address, "$")(1) & "1"
    Next i
End Sub

This code will do the same as your code snippet without changing the active sheet.

Range("A1:D1").Copy
Worksheets("Sheet2").Paste Link:=True

Note that this (and your code) will copy from the active sheet. If you want to copy from a sheet other than the active sheet, use somthing like

Worksheets("Sheet1").Range("A1:D1").Copy
Worksheets("Sheet2").Paste Link:=True

I've had the same problem just now. I just realized then that TightVNC was connected to another machine when I tried to run my code. When I closed it, the code run as usual.

Possibly this happens because some software might be taking control of your clipboard. Just close anything you don't need, like VNCs or Virtual Machines.

You could use Application.ScreenUpdating = False and then return it to true after the paste has completed.

Example:

Application.ScreenUpdating = False
Worksheets("Sheet1").Range("D1").Copy    
Worksheets("Sheet2").Activate   
Range("Range You Want To Paste").Select    
ActiveSheet.PasteSpecial Link:=True  
Worksheets("Sheet1").Activate
Range("A Range You Want Active").Activate   
Application.ScreenUpdating =true

Your LINK desire cannot be done without selecting the sheet. But you can make the fact that it does that invisible to the eye.

Option Explicit

Sub test()
    Application.ScreenUpdating = False

    Sheets("Sheet1").Range("A1:D1").Copy
    With Sheets("Sheet2")
        .Activate
        .Range("A1").Select
        ActiveSheet.Paste Link:=True
    End With
    Sheets("Sheet1").Activate

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub