Need help understanding about unqualified ranges

Question

Can someone help me understand why this works:

Sub CommandButton1_Click()
    Sheets("Inventory Data").Range("A1:G1").Copy
    Sheets("Desig From Inv").Range("A1").PasteSpecial xlPasteValues
End Sub

But, this does not?

Sub CommandButton1_Click()
    Sheets("Inventory Data").Range(Cells(1, 1), Cells(1, 7)).Copy
    Sheets("Desig From Inv").Range("A1").PasteSpecial xlPasteValues
End Sub

I really want the column number to be a variable in the end. Like this:

Sub CommandButton1_Click()
    Sheets("Inventory Data").Range(Cells(1, 1), Cells(1, i)).Copy
    Sheets("Desig From Inv").Range("A1").PasteSpecial xlPasteValues
End Sub

I have tried many forms of getting "Cells" into the "Range".

Thanks!

Try `Sheets("Inventory Data").Range(Sheets("Inventory Data").Cells(1, 1), Sheets("Inventory Data").Cells(1, i)).Copy`. Could have to do with not fully qualifying the `Cells()`. — Kyle, Mar 17 '17 at 18:08
Mat's Mug explained your issue here, but I'd also suggest this as a use case for `Resize()` - e.g. `Sheets("Inventory Data").Cells(1, 1).Resize(1, 7).Copy` since it avoids the scoping problem. — Tim Williams, Mar 17 '17 at 18:38
Thanks Kyle, that helps. I was suspecting something to do with qualifying, but I couldn't visualize why and how to correct it. — Kevin W., Mar 17 '17 at 19:28

Mathieu Guindon · Accepted Answer · 2017-03-17T18:21:53.553

This statement:

Sheets("Inventory Data").Range(Cells(1, 1), Cells(1, i)).Copy

Is really doing this:

ActiveWorkbook.Sheets("Inventory Data") _
              .Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, i)) _
              .Copy

Unless ActiveWorkbook.Sheets("Inventory Data") is the ActiveSheet, the statement is going to blow up, because you're using a range on another sheet to get a range on ActiveWorkbook.Sheets("Inventory Data").

Qualify all the things!

...a With block makes this easier:

With Worksheets("Inventory Data")
    .Range(.Cells(1, 1), .Cells(1, i)).Copy
End With

I'll allow myself a little plug here; I'm working on a (free & open-source) VBE add-in that can easily find these errors for you - paste your code at the bottom of this page (it runs much faster in the actual VBE though) to see everything Rubberduck can find in your code - it's not perfect (yet), but it's better than nothing at all:

Rubberduck inspection results

NICE ! the free encyclopedia – Shai Rado Mar 17 '17 at 18:20 — Shai Rado, Mar 17 '17 at 18:20

score 1 · Answer 2 · answered Mar 17 '17 at 19:31

some other ways:

    Sheets("Inventory Data").Cells(1, 1).Resize(,i).Copy
    Sheets("Desig From Inv").Range("A1").PasteSpecial xlPasteValues

or

    Sheets("Inventory Data").Range("A1").Resize(,i).Copy
    Sheets("Desig From Inv").Range("A1").PasteSpecial xlPasteValues

or

    Sheets("Desig From Inv").Range("A1").Resize(,i).Value = Sheets("Inventory Data").Range("A1").Resize(,i).Value

or

    With Sheets("Inventory Data").Range("A1").Resize(,i)
        Sheets("Desig From Inv").Range("A1").Resize(,i).Value = .Value
    End With

Need help understanding about unqualified ranges

2 Answers2

Linked