VBA sorting error

[nbrown6]

Hi all,

Why does this not work unless im actually on the sheet? This is part of a Userform and as long as i have the 'Runners' sheet open it works fine, but if i have another, it throws an error


Sheets("Runners").Range("A:AA").Sort key1:=Range("D1"), order1:=xlAscending, Header:=xlYes


Cheers

[Bob Phillips]

With Worksheets("Runners")
.Range("A:A").Sort Key1:=.Range("D1"), Order1:=xlAscending Header:=xlYes
End With

[nbrown6]

With Worksheets("Runners")
.Range("A:A").Sort Key1:=.Range("D1"), Order1:=xlAscending Header:=xlYes
End With

Doesnt seem to work?

[Bob Phillips]

What exactly does '... doesn't seem' mean?

[Paul_Hossler]

I think there was typo in XLD's answer.

It should be .Range("A:AA")





With Worksheets("Runners")
.Range("A:AA").Sort Key1:=.Range("D1"), Order1:=xlAscending Header:=xlYes
End With



The important thing is the <dot>Range ("D1") within the With/End With block since that makes Runners the parent object in all cases, otherwise D1 is on whatever the active sheet happens to be

Otherwise a small sample WB would help

[nbrown6]

Right,

This works, but only if the correct sheet is selected.

Sheets("Runners").Range("A:AC").sort Key1:=Range("D1"), _
Order1:=xlAscending, Header:=xlYes

I have a userform inputting data into a form on a separate sheet. The user form has a drop-down list that looks at the sheet, so i would like it to reorder once a new entry is put in and then the drop-down list will be in order

[Paul_Hossler]

Yes, but did you try the code in #5?



The important thing is the <dot>Range ("D1") within the With/End With block since that makes Runners the parent object in all cases, otherwise D1 is on whatever the active sheet happens to be

[nbrown6]

Yes, but did you try the code in #5?

Yep, tried exactly what was written.
I'll try and put it in a sample WB and share it. Knowing my luck it will work on that.

[nbrown6]

Yes, but did you try the code in #5?


Yes, it does work with the little example i just put together. Just had to add the , between the statements.

Ill try it in my project later


With Worksheets("Runners")
.Range("A:AA").sort Key1:=.Range("D1"), Order1:=xlAscending, Header:=xlYes