0

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Collin C.
  • 3
  • 1
  • The problem is that `Range("D2")` is unqualified and defaults to the active sheet. You need With... and `.Range("D2")` as BruceWayne pointed out. – D_Bester Nov 19 '15 at 18:08

3 Answers3

2

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
BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • @ScottCraner - Haha, yeah I was typing my answer out when I saw your comment appear. Not intended to take away your answer :P – BruceWayne Nov 19 '15 at 17:48
  • Thank you so much! I've been trying to learn how to do vba code without .Selects everywhere, and it's made my code cleaner, but it's also made a couple things like this more confusing. Thank you for your help! I really appreciate it! – Collin C. Nov 19 '15 at 18:28
  • @CollinC. "I've been trying to learn how to do vba code without .Selects...", **yes,** that's what I like to hear! It'll do you lots of good. I recommend doing some "standard" excel things and record them with the Macro Recorder. Then, review the code and try to remove *all* `.Select` (basically you just snug up the two lines ending with `.Select` and starting with `Selection.`. Also, here's a [nice SO thread](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) on avoiding `.Select`. Your homework is to read through that :P – BruceWayne Nov 19 '15 at 18:40
  • 1
    Very cool! This is perfect! I'll get to reading this once I finish my current project lol – Collin C. Nov 19 '15 at 18:51
  • @CollinC. - For me, learning to remove `.Select` (and avoid using `.Activate`/`.Active`) really started the ball rolling on writing good macros that I can use over and over with different data. Best of luck! – BruceWayne Nov 19 '15 at 18:56
0

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
Kris B
  • 436
  • 2
  • 3
  • The alternative, instead of declaring a sheet variable and then using it, is to use a 'With' statement. 'With' tells the code that any unsourced references come from that source. ie: 'With Sheets(1)' Followed by a line saying something like '.Range("D2").Formula = "Foobar"' Followed by the closing 'End With'. Note that you still need a "." in front of the Range there, to show that you are referencing an item within the "With" statement. – Grade 'Eh' Bacon Nov 19 '15 at 18:08
  • Just a note - I personally recommend against using `Sheet` (and also `Cell`) as a variable name for a worksheet. This is because it's awfully close to the protected word, `Sheets` and could cause some confusion. Perhaps use `mainWS` or something, which is a little more descriptive and you can see at a glance what worksheet you're working with, instead of a generic word which doesn't really. – BruceWayne Nov 19 '15 at 18:14
  • This method will really help me out, too. I'm going to throw in Batman's improvements and start using it. Thank you! – Collin C. Nov 19 '15 at 18:31
0

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.

devuxer
  • 41,681
  • 47
  • 180
  • 292