Solved: Sorting on each sheet balks

[YellowLabPro]

I am getting an error w/ this block, Applicaton-defined or object-defined error :

For Each wsSource In Workbooks(wbn).Worksheets
lrwSource = Cells(Rows.Count, 1).End(xlUp).Row
With wsSource
Range("A1:K2").Select
.Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2"), _
Order2:=xlAscending, Key3:=Range("A2"), Order3:=xlAscending, Key4:=Range("D2"), _
Order4:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal _
DataOption4:=xlSortNormal
With wsSource


I edited it from this block, recorded. The main edit was adding a 4th sort level. I also want to loop through all the sheets doing this on each sheet.

Range("A1:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("A2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal

[Bob Phillips]

Two things,

1 - you are still not qualifying objects

2 - why the second With wsSource

[mvidas]

1- agree (the Range methods of each KeyX:=)
2- agree
3- They added the ability to sort with 4 keys?

[YellowLabPro]

The second With wsSource, some gremlin added that when I was not looking. It is not in my code.... don't know how it got there.

Not qualifying objects, in this case do I place a "." in front of every Range, including parameters?

I did see one more instance that referencing might be required, but that still does not solve-

.Range("A1:K2").Select
.Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("A2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal


.Range("A1:K2").Select is where it is hanging up on the next sheet.

I also had removed the 4th case to test.
Matt, I thought through the VBA side the 3 level sort could be overted.... guessing now by your post that is not the case.

[Bob Phillips]

Taking into account Matt's comment, it would be



For Each wsSource In Workbooks(wbn).Worksheets
With wsSource
lrwSource = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A1:K2").Select
.Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("C2"), Order2:=xlAscending, _
Key2:=Range("A2"), Order3:=xlAscending, _
Key3:=Range("D2"), Order4:=xlDescending, _
Header:=xlGuess
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Header:=xlGuess

[YellowLabPro]

Matt-
missed the last part of point #1- Got that.
when looping through the sheets, still getting hung up on .Range("A1:K" & lrwSource).Select


.Range("A1:K" & lrwSource).Select
Selection.Sort Key1:=.Range("B2"), Order1:=xlAscending, Key2:=.Range("C2") _
, Order2:=xlAscending, Key3:=.Range("A2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal

[Bob Phillips]

Without the selecting



For Each wsSource In Workbooks(wbn).Worksheets
With wsSource
lrwSource = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range("A1:K2")
Set rng = .Range(rng, rng.End(xlDown))
Application.CutCopyMode = False
rng.Sort Key1:=Range("C2"), Order2:=xlAscending, _
Key2:=Range("A2"), Order3:=xlAscending, _
Key3:=Range("D2"), Order4:=xlDescending, _
Header:=xlGuess
rng.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Header:=xlGuess

[mvidas]

Bob, looks like the copy/paste from your VBE didn't stick

Dougie Max, if the worksheet isn't active then the Range.Select won't work

But re: not qualifying objects, when you have
Key1:=Range("C2") That should be:
Key1:=.Range("C2")

In total:For Each wsSource In Workbooks(wbn).Worksheets
With wsSource
lrwSource = .Cells(.Rows.Count, 1).End(xlUp).Row
With .Range("A1", .Range("A1").End(xlDown).Offset(0, 10))
.Sort Key1:=.Range("C2"), Order1:=xlAscending, _
Key2:=.Range("A2"), Order2:=xlAscending, _
Key3:=.Range("D2"), Order3:=xlDescending, _
Header:=xlYes
.Sort Key1:=.Range("B2"), Order1:=xlAscending, _
Header:=xlYes
End With 'range

[YellowLabPro]

I did not even see the reference needed in the .Rows.Count.

But still hanging up on the first .Range line when looping to the second sheet.
I am thinking it is because the sheet is not getting activated, so it cannot Select.
I will try and rework, the code not to select, but that seems necessary to perform the sort.

With wsSource
lrwSource = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A1:K2").Select
.Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Sort Key1:=.Range("C2"), Order1:=xlAscending, _
Key2:=.Range("A2"), Order2:=xlAscending, _
Key3:=.Range("D2"), Order3:=xlDescending, _
Header:=xlGuess
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Header:=xlGuess
.Range("A2:J" & lrwSource).ClearFormats
End With


In my re-worked code, I have removed the selection.end .... down.
But still have the same consistent issue on the .Range("A1:K" & lrwSource).Select


With wsSource
lrwSource = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A1:K" & lrwSource).Select
Selection.Sort Key1:=.Range("B2"), Order1:=xlAscending, Key2:=.Range("C2") _
, Order2:=xlAscending, Key3:=.Range("A2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal

[mvidas]

FYI, due to posting times you might miss the post above your most recent one. You can always use the lrwSource in the With block instead of the way I did it (which is equivalent to your selection)

My question about the 4 sort keys was half-true; I still have not tried excel 2007 and thought maybe you had it and it allowed 4 keys :)

[rory]

Per Matt:

if the worksheet isn't active then the Range.Select won't work
You should be trying to avoid selecting things anyway as it's inefficient and almost never necessary.

[YellowLabPro]

Thanks Matt,
I did not see it until after I posted. Got it to work- It made sense once I watched what was going on.

Both your's and Bob's answers are really good and insightful. Matt- yours is nice for showing me how to use the Offset- I have not started thinking that way just yet, but it is on the horizon.

Bob- your is also very powerful, setting separate ranges up, I have started using them in smaller logical issues, so hopefully this will creep along and find its way into more uses of my code.

ps. Matt,
Don't know if I have ever properly thanked you for the reg.expression solution you gave me. It works fantastic, I get to use it about twice a week and is a big help. So if I did not already, Thank you for that too.

Thanks Gents,

Doug

[YellowLabPro]

Rory,
Yes, you are correct, but I was thinking for the sort, that maybe this was one of the exceptions to that advice. But I see now that selecting here is not necessary.

thanks

[mvidas]

ps. Matt,
Don't know if I have ever properly thanked you for the reg.expression solution you gave me. It works fantastic, I get to use it about twice a week and is a big help. So if I did not already, Thank you for that too.
... Memory is drawing a blank.. was yours the one where I separated the colors out/etc? :) In any case you're welcome

If you're just using AWorksheet.Paste you can't avoid selecting, however you dont have to use that method of pasting either (plenty of alternate opportunities). Using ARange.PasteSpecial does select cells too but you don't have to actually use a select statement for that, so its moot. I can't think of anything I may do that actually requires selecting a cell or worksheet

[YellowLabPro]

Yes, the color one is correct....
Agree w/ your other points....