2

How to avoid select with copy named Range and paste as link , 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
Leedo
  • 439
  • 5
  • 18

3 Answers3

3

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.

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
1

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
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
0

What about avoiding copy/paste

Sub Paste_Named_Range()

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

End Sub
Ike
  • 9,580
  • 4
  • 13
  • 29
  • Dose, not work at all – Leedo Sep 20 '21 at 08:45
  • 1
    what do you mean by "does not work at all"? Do you get an error? Is the result not as expected? The result should be "= Quarter_1" as a formula in cell O68 ... – Ike Sep 20 '21 at 08:50
  • It insert Quarter_1 as string , I even modified it ( corrected) but not works also – Leedo Sep 20 '21 at 09:04
  • Again: what does not work? If you type manually "= Quarter_1" into the cell - does it show the correct value? Or is my understanding not correct that Quarter_1 is a named cell? – Ike Sep 20 '21 at 09:12