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

 

[RoryA]

Steve,
You haven't specified the sheet for the Cells properties:

Dim CodeRange As Range

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

CodeRange.Value = MyCodeArray

or neater:

Dim CodeRange As Range
With Worksheets("All")
   Set CodeRange = .Range(.Cells(3, "U"), .Cells(iLast, "U"))
End With

CodeRange.Value = MyCodeArray

Note the periods before the Cells properties that relate them back to the sheet specified in the With block.

 

[lacklogic]

THANK YOU ALL... RORYA, garden_rael, and gaj104... these have been very helpful tips!

RORYA: I didn't realize that the "cells" property was defaulting to the active sheet due to my not specifying the parent objects... as I did with the initial range object. That explains why IT DOES work when I have the target sheet in focus, but if when I was running the macro from any other sheet... failure. This makes total sense now!

Also the named range idea is a great work around, unfortunately the range in this case is dynamic? unless there is a way to name a "dynamic range" which I am not aware of.

Thanks again guys! Mr. Excel Forum offers up yet another quick intelligent set of answers with out fail... appreciate you all for taking the time to help!

SW

 

[mhageman]

Hello guys,

I have a similar desire but a little different. I have not found this specifically yet so I am posting here because this thread is the freshest I have found.

I have a list of names (~600 in a different sheet) and I need to have the ability for multiple selection but I also would like the added effect that the user can type in part of the name and it populate the list form the matching text that was entered. Similar to the combo box ActiveX. I was think that if there was some way to use the list that is generated by the ctrl+f "find all" then that really would be usefully. I don't know if recording a macro will tell me where ctrl+f stores the list temporarily or not, that is the next thing I plan to try. Why I want to use the ctrl+f is due to that some of the names that will be needed to be selected will have the same part of text just in different spots in the names. Some will be at the start, or the middle, or the end, or somewhere else.
Ex.
BOB_MN_0
BOB_MX_0
BOB_MN_1
BOB_MX_1
BOB_MN_2
BOB_MX_2
I want the list to allow the user to type MX and show this list
BOB_MX_0
BOB_MX_1
BOB_MX_2
and permit them to select all that apply. (Say 0 and 2 but not 1)
Any thoughts?
Thank you,
-Mark

 

[Mike1001]

I'm sure it appears strange what you can do and what you cannot do.

The following code will select the desired range after sorting. I'm not sure if that helps, but it's a possibility I found useful in some instances.

    With ActiveWorkbook.Worksheets("Dir").Sort
      .SortFields.Clear
      .SortFields.Add Key:=Range("B2:B10"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SetRange Range("B2:B10")
      .Header = xlNo
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With

HTH

 

[MrZNF]

THANK YOU ALL... RORYA, garden_rael, and gaj104... these have been very helpful tips!

RORYA: I didn't realize that the "cells" property was defaulting to the active sheet due to my not specifying the parent objects... as I did with the initial range object. That explains why IT DOES work when I have the target sheet in focus, but if when I was running the macro from any other sheet... failure. This makes total sense now!

Also the named range idea is a great work around, unfortunately the range in this case is dynamic? unless there is a way to name a "dynamic range" which I am not aware of.

Thanks again guys! Mr. Excel Forum offers up yet another quick intelligent set of answers with out fail... appreciate you all for taking the time to help!

SW

Not sure how it is with bumping on this forum, but I've used this website so much to find answers and this is a question I know the answer to, I think (so I signed up and wanted to post):
In this video: https://www.youtube.com/watch?v=vkPoViUhkxU Neil Firth explains how to create a searchable dropdown list. Skip to 8:30 for an explanation of the dynamic range he creates with Offset, using the name manager.
I think you could refer to this dynamic range and presto

I hop it will help someone (and sorry for the bump)
Cheers,
Zeno

 

[Nanzyz]

excelenteeeee!!!! Gracias por el codigo,

 

[UDAEY]

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.

Check the website : https://docs.microsoft.com/en-us/of...eveloper/select-cells-rangs-with-visual-basic. Go to the section : How to Select a Cell on a Worksheet in a Different Workbook. You may find answer.

 

[UDAEY]

Even the following page may be useful : https://www.engram9.info/excel-2003...between-using-select-and-applicationgoto.html

 

[TrashmanL]

Another way to select a range in a different sheet it to use

.parent.select

first, then use

.select