Replace Window.Activate > Sheet.Select > Range.Select with a single command?

[d0rian]

Forgive my noob Q, but I'm mostly self-taught :/

I want to go to a 2nd open workbook, go to a specific sheet within that workbook, and select a specific range on that sheet. I currently use 3 lines:

Windows("data_file.xlsm").Activate
Sheets("raw_data").Select
Range("control_range").Select

That can't be all that efficient...though it's my understanding that I can't SELECT the control_range range unless the sheet it's on is active.

So is there a 1-line command I can use to tell Excel to go to a specific range on a 2nd open workbook?

 

[Fluff]

If you need to select the range, try

Workbooks("data_file.xlsm").Activate
Application.Goto Sheets("raw_data").Range("control_range"), True

However if you just want to copy the range you can use

Workbooks("data_file.xlsm").Sheets("raw_data").Range("control_range").Copy

 

[d0rian]

Thank you -- what does the "True" argument in the first snippet do exactly?

Also, while we're on the topic, I don't quite understand why I can't Copy a range that's not on the currently-active sheet. Like imagine there were only 1 workbook open, and I wanted to Copy a range that wasn't on the active sheet, it seems like I'd still need to include the entire Workbooks("data_file.xlsm").Sheets("raw_data").Range("control_range").Copy path in the code...which just strikes me as odd because I can use a name ONCE to define a range...e.g. in my example, my workbook can only have a single range named "control_range", so why do I have to specify the full Workbooks/Sheets/Range path when there's no risk of ambiguity?

(I've also noticed that only SOME functions have this limitation...for example, I can use ClearContents on a range that's not on the active sheet, but Copy doesn't work, which again seemed odd.)

 

[Fluff]

what does the "True" argument in the first snippet do exactly?

Means that the window will scroll to the specified range, otherwise the range will be selected but it might not be in view.

With named ranges, if the scope is to the sheet you can do

Sheets("Page1B").Range("Header").Copy ActiveCell

However if it's scope is Workbook you can do

Range("Header").Copy ActiveCell