Sort issue

[Guest]

Hi,

I am trying to sort a group of cells on another page with vba. I keep
getting a select method of Range Class Failed error. What am I doing wrong?
I have checked all variables and they have the correct values.

Tablespg.Range(ItemCol & ItemFRow & ":" & PoolCol & Lusedrow).Select
Selection.Sort Key1:=Range(ItemCol & ItemFRow), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

I also tried:

Tablespg.Range(ItemCol & ItemFRow & ":" & PoolCol & Lusedrow).Select
Selection.Sort Key1:=Tablespg.Range(ItemCol & ItemFRow), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[Guest]

You kind of answered your own question. You cannot select a range that is not
on the activesheet. You can either select the Tablespg first or rewrite your
code to work without selecting. Let me know if you need help with either
solution.

 

[Guest]

Hi Charles,

I had tried that as well and it did not work so I switched to select. I am
not at work now but I believe this is what I tried:

Tablespg.Range(ItemCol & ItemFRow & ":" & PoolCol & Lusedrow). _
Sort Key1:=Range(ItemCol & ItemFRow), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[Guest]

Hi Charles,

It doesn't appear my post processed correctly. Thank you for your help.

I had tried to eliminate select but it didn't work wo I switched to select.
I'm not at work now but I believe this is what I tried.

Tablespg.Range(ItemCol & ItemFRow & ":" & PoolCol & Lusedrow). _
Sort Key1:=Range(ItemCol & ItemFRow), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Did I miss something?

 

[Dave Peterson]

You didn't qualify your key1 range:

Tablespg.Range(ItemCol & ItemFRow & ":" & PoolCol & Lusedrow). _
Sort Key1:=tablspg.Range(ItemCol & ItemFRow), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

And if I know my data, I don't allow excel to guess if I have headers or not. I
use xlyes or xlno--not xlguess.

 

[Karen53]

Thank you, Dave!