absolute relative reference

[Guest]

Good day,

Situation:

I am having a macro in "sheet_1" that is doing a whole bunch of operations.
The macro uses a combination of absolute references such as:

Cells(1, "C") = ...

as well as relative references such as:

Range("B1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 1).Select

My problem:

When I need the macro to execute an operation in the next sheet of my
workbook "sheet_2", I write something as simple as:

Sheets("sheet_2").Select
Range("A3:A200").Select

but I have an error message at the second line "Range("A3:A200").Select". I
have never seen this kind of error before. My second line must be written as

Sheets("sheet_2").Range("A2:K2").Select

Usually, When I select a sheet, I do not need to "re-select" it in each line
of the macro, but for this one, I am obliged to do so and it gets quite
annoying.

I think there is a problem of reference (absolute vs relative). Or maybe my
macro is dedicated to sheet_1 only? If this is the case, how do I make it
valid for the whole workbook? In any case, please help!

 

[Guest]

Sebastien,

Perhaps your code is inside a worksheet module? If so, it would explain why
you need to qualify the Range selection. If you don't qualify it, Excel
would attempt to select the range that exists in the worksheet where the code
is. But because that worksheet is no longer the active one, you get the
error. To avoid the error, you will need to move your Sub to a module.

 

[Dave Peterson]

I think Vergel answered your question, but you could use:

with worksheets("sheet2")
.select
.range("a1:b9").select
end with

It'll save you some keystrokes and I think makes the code easier to read.