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
Improve this question
6
  • 1
    Sheet03.[O68].Paste, avoiding select Commented Sep 20, 2021 at 7:43
  • Does this answer your question? How to avoid using Select in Excel VBA Commented Sep 20, 2021 at 7:43
  • I need to paste as a link , and I already read that great post. Commented Sep 20, 2021 at 7:45
  • What about Sheet03.[O68].Paste Link:=True? Commented Sep 20, 2021 at 7:45
  • It gives error "syntax error" Commented Sep 20, 2021 at 7:56

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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
Improve this answer

Comments

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
Improve this answer

4 Comments

Dose, not work at all
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 ...
It insert Quarter_1 as string , I even modified it ( corrected) but not works also
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?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.