CODEWITHSUNDEEP
excelvba
Collin C.
Collin C.

Reputation: 3

Difference between Sheets().Range() and Sheets().Column

Why can I use: 

Sheets("SKU Opt").Columns("D:D").Copy

from any sheet, but I can only use:

Sheets("SKU Opt").Range(Range("D2"), Range("D2").End(xlDown)).Copy

from the 'SKU Opt' sheet?

I get a "Application Defined or Object Defined Error" when I try to run the second one from any sheet besides the 'SKU Opt' sheet.

Upvotes: 0

Views: 477

Answers (3)

devuxer
devuxer

Reputation: 42364

Another variation on the other answers:

Dim firstCell, lastCell As Range
Set firstCell = Sheets("SKU Opt").Range("D2")
Set lastCell = firstCell.End(xlDown)
Range(firstCell, lastCell).Copy

Notice that lastCell is derived from firstCell and that the sheet only needs to be referenced when setting firstCell.

Upvotes: 0

Kris B
Kris B

Reputation: 446

Personally I like to use the following format at the top of my code. Its possible that more than one workbook is open in the same excel instance (application). With the same sheet name, so you want to specify the workbook as well to be even more safe.

This also has the added benefit of making your code a bit more easy to read!

Dim sheet as Worksheet

set sheet = ThisWorkbook.Worksheets("SKU Opt")

sheet.Range(sheet.Range("D2"), sheet.Range("D2").End(xlDown)).Copy

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

You need to change that to

Sheets("SKU Opt").Range(Sheets("SKU Opt").Range("D2"), Sheets("SKU Opt").Range("D2").End(xlDown)).Copy

When working with multiple worksheets, you should always qualify the worksheet you're working with. As you see, when you don't, it's possible to get errors. As you have it, it's looking to the "SKU Opt" page for a range, but then what range isn't exactly clear. Without specifying, the Range() set will be from the Active Sheet. When the Active Sheet is different than "SKU Opt", it'll throw an error most likely.

An alternative is to use With:

With Sheets("SKU Opt")
   .Range(.Range("D2"),.Range("D2").End(xlDown)).Copy
End With

Upvotes: 2

Related Questions