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

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Location
    Portugal
    Posts
    343
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

     
    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

  2. #2
    Board Regular Todd Bardoni's Avatar
    Join Date
    Aug 2002
    Location
    Rochester, MI USA
    Posts
    3,042
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - How to select a range in a non-activated worksheet

    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?
    Todd


    "I'm a Data Anlyst(intermediate) and have no idea what it's all about."

  3. #3
    Board Regular
    Join Date
    Jul 2002
    Location
    Portugal
    Posts
    343
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - How to select a range in a non-activated worksheet

    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

  4. #4
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,853
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - How to select a range in a non-activated worksheet

    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.

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,129
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - How to select a range in a non-activated worksheet

    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.

  6. #6
    Board Regular
    Join Date
    Jul 2002
    Location
    Portugal
    Posts
    343
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - How to select a range in a non-activated worksheet

    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

  7. #7
    New Member
    Join Date
    Sep 2011
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - How to select a range in a non-activated worksheet

    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:

    Code:
    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

  8. #8
    Board Regular garden_rael's Avatar
    Join Date
    Apr 2008
    Location
    Monterrey
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - How to select a range in a non-activated worksheet

    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.

  9. #9
    Board Regular gaj104's Avatar
    Join Date
    Nov 2002
    Location
    London, UK
    Posts
    864
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - How to select a range in a non-activated worksheet

    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:

    Code:
    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.

  10. #10
    Board Regular garden_rael's Avatar
    Join Date
    Apr 2008
    Location
    Monterrey
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - How to select a range in a non-activated worksheet

      
    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.
    Last edited by garden_rael; Feb 23rd, 2012 at 04:18 PM.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com