Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a requirement to select a workbook or worksheet to ensure that the filter (if there is one) is null?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Who would impose that requirement? Gordon Brown?


  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No, it is a habit that I have acquired, but was wondering if the selection was needed?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    No, you can use code like this:

    Workbooks("OtherWorkbook.xls").Worksheets("SomeShe et").ShowAllData

    There is no need to activate Otherworkbook.xls or SomeSheet.


  5. #5

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    But:

    (I always get cunfused with this "selection" business)

    To sort a range, like so:

    Code:
        Set y1a = w1a.Range("A2:R" & x1a)
            y1a.Sort Key1:=Range("K2"), Order1:=xlAscending, Key2:=Range("L2"), Order2:=xlAscending, Header:=xlNo
    where w1a is the worksheet, that has to be active, right??

    No "actual selection" is made, but if the sheet is not active, I get:

    [attachment=86670:Sort.GIF]

    Attached Images Attached Images

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    No, w1a doesn't have to be the active sheet. The problem here is that the sort keys Range("K1") and Range("L2") do refer to the active sheet because you don't tell VBA that they are on w1a. But of course you can't sort w1a on keys on another sheet! You must specify explicitly that the sort keys are on w1a:

    Set y1a = w1a.Range("A2:R" & x1a)
    y1a.Sort Key1:=w1a.Range("K2"), Order1:=xlAscending, Key2:=w1a.Range("L2"), Order2:=xlAscending, Header:=xlNo

  8. #8

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Another little'un if I may:

    Set v2 = Workbooks.Open(Filename:="C:\Users\Nathan\Document s\mybook.xls", ReadOnly:=True)

    This then becomes the active wb, at front, so I currently use an activate command to flick back to my prime wb. Is there any way to open workbooks but not have them becoming active, to front?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by VegasNath View Post
    Is there any way to open workbooks but not have them becoming active, to front?
    No, when you open a workbook it will always become the active workbook. You could hide it immediately after opening it, by using

    ActiveWindow.Visible = False

    If you don't want to hide it, you'll have to reactivate the previously active workbook explicitly.

Posting Permissions

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