0

I have VBA to the effect of

Dim address As String
address = Worksheets("Sheet1").Cells(1,1).Address
Worksheets("Sheet2").Range(address).Select

In debug mode, I see that

address="$A$1"

However, I am unable to use this value, and get the error

Run-time error '1004':
Select method of Range class failed

Any tips on why this is, and how to fix it? I was under the impression that any string could be placed into Range().

Community
  • 1
  • 1
  • 4
    the problem is that you cannot `Select` a Range on a sheet that is not Active. If you write `debug.print Worksheets("Sheet2").Range(address).Value` it will return the value of Sheet2!A1. Also you can add `Worksheets("Sheet2").Activate` before the `Select` statement and it will work. – Scott Holtzman Jul 19 '16 at 19:54
  • Ah, that worked! So I also tried using `Worksheets("Sheet2").Select`, and that worked. What's the difference between `Activate` and `Select`? [Edited for formatting] –  Jul 19 '16 at 19:59
  • 2
    Not much, although its best practice to [stay away from them](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) unless absolutely necessary. – Scott Holtzman Jul 19 '16 at 20:01
  • I'll keep that mind. Thanks! –  Jul 19 '16 at 20:03

0 Answers0