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?
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.
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