How to avoid select with copy named Range as in the below code

Sub Paste_Named_Range()

If Sheet03.[G60] = "3rd Q" Then

    Sheet03.[Quarter_1].Copy
    Sheet03.[O68].Select
    Sheet03.Paste Link:=True

 End If

End Sub

CodePudding user response：

This is a special case. Because you are using the Link argument = True, I'm afraid you must first select the destination.

Worksheet.Paste method (Excel)

In the documentation about Link:

True to establish a link to the source of the pasted data. If this argument is specified, the Destination argument cannot be used. The default value is False.

And because we are forced to omit the Destination argument, then:

If this argument is omitted, the current selection is used. This argument can be specified only if the contents of the Clipboard can be pasted into a range.

Because all of this, I'm afraid you can't avoid Select in this case. So your code should be exactly as you have it:

Sheet03.[Quarter_1].Copy
Sheet03.[O68].Select
Sheet03.Paste Link:=True

Good question. I did not know about this fact. Let's see if maybe other user can help you out, but after reading documentation, I'm afraid not.

CodePudding user response：

Copy a Simple Formula Instead of Paste Link:=True

• The following will work properly only if Quarter_1 is a contiguous (single) range.

Option Explicit

Sub Paste_Named_Range()
    
    ' Works only for a contiguous (single) range (a range with one area).
    Dim srg As Range: Set srg = Sheet03.[Quarter_1] ' .Areas(1)
    Dim drg As Range
    Set drg = Sheet03.[O68].Resize(srg.Rows.Count, srg.Columns.Count)
    
    If Sheet03.[G60] = "3rd Q" Then
        drg.Formula = "=" & srg.Cells(1).Address(0, 0)
    End If
    
End Sub

CodePudding user response：

What about avoiding copy/paste

Sub Paste_Named_Range()

If Sheet03.[G60] = "3rd Q" Then
    Sheet03.[O68].Formula = "=Quarter_1"
End If

End Sub