How to avoid selecting Dynamic rows in a with loop vba

Question

I have been using the code below just fine for the past few months. But i have just discovered that because i am selecting a dynamic range in a worksheet, if i am not IN the worksheet, i get select method range class failed.

The below code fails:

With w.Sheets("Sheet1")
        n = .Cells(Rows.Count, 2).End(xlUp).Row
        .Range("Y2:AI2").Copy
        .Cells(n, 25).Select
        .Range(Selection, Selection.End(xlUp).Offset(1, 0)).PasteSpecial xlPasteFormulas
        .Application.CutCopyMode = False
    End With

The following code succeeds:

With w.Sheets("Sheet1")
        n = .Cells(Rows.Count, 2).End(xlUp).Row
        .Range("Y2:AI2").Copy
        Sheets("Sheet1").Select
        .Cells(n, 25).Select
        .Range(Selection, Selection.End(xlUp).Offset(1, 0)).PasteSpecial xlPasteFormulas
        .Application.CutCopyMode = False
    End With

I have to select the sheet again. Ideally i want to avoid selecting/activating altogether!

Any steer in the right direction will be greatly appreciated!

you _apparent_ need for selecting sheet is due to `.Cells(n, 25).Select` statement for which you'd actually need to _be_ in that cell sheet. But you don't need to select that cell: see my answer — user3598756, Dec 20 '16 at 10:07
There is really few case where the use of `.Select` is mandatory in vba. See [How to avoid using Select in Excel macro](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) post. — Vincent G, Dec 20 '16 at 10:25

user3598756 · Accepted Answer · 2016-12-20T10:03:27.713

4

try this

With w.Sheets("Sheet1")
    .Range("Y2:AI2").Copy
    With .Cells(.Cells(Rows.count, 2).End(xlUp).row, 25)
        .Parent.Range(.Cells, .End(xlUp).Offset(1, 0)).PasteSpecial xlPasteFormulas
    End With
    .Application.CutCopyMode = False
End With

edited Dec 20 '16 at 10:03

answered Dec 20 '16 at 09:48

user3598756

28,893
4
18
28

Thanks for that. Would you mind explaining the parent property? This worked perfectly – mojo3340 Dec 20 '16 at 16:09
You are welcome. Inside a `With someRange` block, a _simple_ `.Range(...` statement would point to a `range` relative to the _referenced_ `range` (e.g. inside a `With Range("A10")` block, `.Range("A1:A10")` would point to cells A10:A19). While `.Parent.Range(...` makes you _climb up_ one level in Excel model object hierarchy and reach the `worksheet` object thus getting you in the _good ol'_ position of referencing a `Range` relative to a worksheet (e.g. inside a `With Range("A10")` block, `Parent.Range("A1:A10")` would point to cells A1:A10, at last!). – user3598756 Dec 20 '16 at 18:09

How to avoid selecting Dynamic rows in a with loop vba

1 Answers1