Referencing a range from a different Sheet problem

[kyrgyzstanart]

Hi,

Here is the the problem:

1. 2 Worksheets.
2. A table w/ data on the first one.

When I activate manually Sheet 1 and run the code it runs smoothly. But when I activate manually Sheet 2 and run the code it gives me an error message - Runtime error '91' - Object variable or With block not set.

And I really don't understand it 'cause in the code I do activate Sheets(1) before doing anything. So it's a mystery for my why it would not run correctly if launched from any sheets other than Sheets(1).

Please Login or Register  to view this content.

[royUK]

Try this

Please Login or Register  to view this content.

If you use Sheets(1), the code will activate the first sheet, not necessarily Sheet1. This way of referring to a sheet can cause problems if the position of the sheets is changed. The code I posted will work on Sheet1, which is the Code name of the sheet. Also, it does not need to activate the sheet.

[kyrgyzstanart]

Hi RoyUK,

One question, if I do it w/ WITH structure, will I be able to use Data_Raw_Cell variable (w/ assigned to it Sheets(1) Range) in Functions on the Second (Index) Sheet?

[royUK]

Hi RoyUK,

One question, if I do it w/ WITH structure, will I be able to use Data_Raw_Cell variable (w/ assigned to it Sheets(1) Range) in Functions on the Second (Index) Sheet?

I'm not sure what you mean