CODEWITHSUNDEEP
excelvba
Steveng
Steveng

Reputation: 1521

Paste link in vba

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.

Upvotes: 1

Views: 34397

Answers (6)

Hotwire20
Hotwire20

Reputation: 13

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

Upvotes: -1

Victor Braga
Victor Braga

Reputation: 1

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.

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149295

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

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53127

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

Upvotes: 0

Jerry Beaucaire
Jerry Beaucaire

Reputation: 3197

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

Upvotes: -1

Tahbaza
Tahbaza

Reputation: 9548

This will work:

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

Upvotes: 2

Related Questions