VBA - How to select a range in a non-activated worksheet

[MrDoc]

Hello,

I need help on a very simple matter (I think...):
When I try to use

Sheets("Dir").Range("B2:B10").Select

in VBA code, there is an error "Select method of range class failed",
and I have to use

sheets("Dir").activate
range(B2:B10").select

What am I doing wrong?
I really don´t want to activate the "Dir" worksheet, just select a range on it and work on that range - sort it, for instance.

Thank you for any help.
Best Regards,
MrDoc

 

[Todd Bardoni]

You're not doing anything wrong. You MUST activate the sheet before selecting a range on that sheet.

What exactly are you trying to do and why do you not want to activate the sheet?

 

[MrDoc]

Thank you for your reply. I'm sorting a range in "Dir" worksheet, and then copying part of it to "TronX" worksheet, where X is the highest value in the existing Tron1, Tron2, TronN....worksheets. So I'm not using sheets.count, because it depends on the sheet tab position.
Now, having to activate a sheet and then the other, and repeating it again and again, becomes somehow confusing, mainly because I'm editing code I wrote a long time ago.
But maybe I can sort a range in "Dir" worksheet without selecting it??
Best Regards,
MrDoc

 

[DRJ]

In most cases you will need to activate the sheet you want to work with first. If you do not want events to trigger because you activated the sheet, or deactivated the current sheet you can use (application.enableevents=false) to turn off triggered events. Just remember to turn them back on later. If you do not want the user to see the sheets changing as different sheets are activated use (application.screenupdating=false) to stop the screen from updating until the code is finished running.

If this doesnt help post more info on what you are trying to do.

 

[Tom Urtis]

You don't need to select / activate a sheet in order to sort a range on it.

Based on your posted example, one syntax could be:

With Sheets("Dir")
.Range("B1:B10").Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlYes
End With

Assuming B1 contains a header value.

 

[MrDoc]

Thank you all for helping. I think Tom's reply is just what I was looking for in this particular case - not having to activate sheet1 and then sheet2 and then sheet1 and... in my code. I'll try it as soon as I can. I really expected that to be possible, since it's also possible to change cell values using VBA even if the worksheet is hidden, but I couldn´t figure out how to do it.
Best Regards,
MrDoc

 

[lacklogic]

Tom, Anyone,

I was just reading this post here... it seems to be a situation that is strikingly similar to what I am trying to do... but I haven't quite determined the solution (yet). Wondering if anyone could tell me what I am doing wrong in my thinking here:

Dim CodeRange As Range

Set CodeRange = Worksheets("ALL").Range(Cells(3, "U"), Cells(iLast, "U"))

CodeRange.Value = MyCodeArray

So... this code works perfectly fine if I run the macro while the "ALL" sheet (which is also referenced by the above code, as you can see) is the active sheet. But if I run this from any other sheet, it gets stuck on the 2nd line with the "Set" statement. Does the set statement require the target range to be on the "active sheet" ?

Thanks in advance for any advice you can give me!!! Thanks again.

Steve Wallace

 

[garden_rael]

You can also name the range and then you can just use anywhere without activating.
Using range will always require you to be in the proper sheet. At least that's what I've found.

 

[gaj104]

Using range will always require you to be in the proper sheet.

Not true. Excel is object orientated. Make reference to the parent and you'll never need to activate anything.

For example:

Workbooks("mybook").Sheets("mysheet").Range("A1:A10").Value = "Test"
Workbooks("mybook2").Sheets("mysheet").Range("A1:A10").Value = "Test2"

This will populate "Test" in the sheet "mysheet" in workbooks "mybook" and then populate "Test2" in mybook2 workbook in the sheet "mysheet".

I could run this in any workbook and it would still have the same outcome.

If you havent referenced the 'parent' then excel will assume it is the active cell/sheet/workbook or even application.

 

[garden_rael]

I've got error using that many many times, sometimes errors that no one can "explain". So one solution that worked for me was naming the range and I've never have that problem again. Still, I reference the parent workbook as sometimes I work with more than 1 workbook.

You can get the data out the ranges using your code. But sometimes when what you want is actually selecting the range, it just won't do it.