CODEWITHSUNDEEP
vbaexcel
Niva
Niva

Reputation: 298

Implement Paste Link for this code

I have this code which allows to a copy a customized range from any sheet and paste it to a fixed range on sheet 2. This code works but I need to implement paste link function in this code, so that if i want to make any changes to the data in DB it will auto update in sheet 2 as well. Here is the code I have done so far. Thank you in advance

Sub CustomizedInputFixedoutput()


Dim rng As Range, _
 inp As Range, _
 ws As Worksheet


Set inp = Selection
On Error Resume Next
Set rng = Application.InputBox("Copy to", Type:=8)
On Error GoTo 0
If TypeName(rng) <> "Range" Then
    MsgBox "Cancelled", vbInformation
    Exit Sub
Else
    rng.Parent.Activate
    rng.Copy
    Sheets("Sheet 2").Range("B2:N5").Value = rng.Value
End If
Application.CutCopyMode = False

End Sub

Upvotes: 2

Views: 257

Answers (2)

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

I felt sure this had to be a duplicate but searching [excel-vba] Paste Link found a few questions without any accepted answers and none that matched to OP desire to paste into a specific range.

Option Explicit

Sub CustomizedInputFixedoutput()
Dim CopyRng As Range
Dim PasteRng As Range
Dim Msg As String
Dim Response As VbMsgBoxResult


    Set CopyRng = Selection
    On Error Resume Next
    Set PasteRng = Application.InputBox("Select a cell to copy to", Type:=8)
    On Error GoTo 0
    If Not PasteRng Is Nothing Then 'user clicked Cancel
        If PasteRng.Count > 1 Then
            'Get confirmation to paste to multi-cell range
            Msg = "Are you sure you want to paste to " & PasteRng.Address & "?" _
            & vbCrLf & vbCrLf _
            & "Results may be unexpected if you proceed."
            Response = MsgBox(Msg, vbQuestion + vbYesNo, "Confirm multi-cell paste range")
        End If
        If Response = vbYes Or PasteRng.Count = 1 Then
            CopyRng.Copy
            PasteRng.Parent.Activate
            PasteRng.Activate
            ActiveSheet.Paste Link:=True
        Else
            MsgBox "Cancelled", vbInformation
        End If
    Else
        MsgBox "Cancelled", vbInformation
    End If
    Application.CutCopyMode = False
End Sub

Upvotes: 2

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Here you copy the range:

rng.Copy

And here you are assigning the value of B2:N5 the same value as rng.

Sheets("Sheet 2").Range("B2:N5").Value = rng.Value

The problem is that that code isn't pasting anything from the clipboard! You don't need to .Copy anything to assign cell values like this.

Use the Worksheet.Paste method instead of assigning the values (then the .Copy will serve its purpose), and set the optional parameter Links to True, like this:

Worksheets("Sheet 2").Range("B2:N5").Select
Worksheets("Sheet 2").Paste Links:=True

Upvotes: 1

Related Questions