Ensure that filter is null

[VegasNath]

Is there a requirement to select a workbook or worksheet to ensure that the filter (if there is one) is null?

[HansV]

Who would impose that requirement? Gordon Brown?

[VegasNath]

No, it is a habit that I have acquired, but was wondering if the selection was needed?

[HansV]

No, you can use code like this:

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

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

[VegasNath]

Brilliant, Thanks!

[VegasNath]

But:

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

To sort a range, like so:

    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]

[HansV]

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

[VegasNath]

Arh, got'ya! Thankyou very much!!

[VegasNath]

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?

[HansV]

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.