Sort Method
 

I have the following code. For some reason Key2 is not being sorted the way
I need it. For my santity, I tried switching between ascending/descending
in key2 and both results same. Any ideas why? The sort range is columns
A:AJ. Column B contains numbers and Column D contains dates.


Private Sub Workbook_BeforeClose(Cancel As Boolean)

'sort contract workbook (Sheet1) only if Settlement4b.xls is not open

Dim Wsheet As Worksheet
Set Wsheet = Worksheets("Sheet1") 'Contracts workbook

If Not WorkbookOpen("Settlement4b.xls") Then
With Wsheet
.Select
.Range("A1").Sort _
Key1:=.Range("B1"), Order1:=xlAscending, Header:=xlYes, _
Key2:=.Range("D1"), Order1:=xlDescending, Header:=xlYes
End With
End If


As aways Thanks!
Mike

End Sub

Sort Method
 

A few things. You don't want to sort range A1, you probably want
Range("A1").CurrentRegion, or maybe Range("A1:AJ500")
Next - you don't want to say Header:=xlyes twice, tho it doesn't seem to
hurt.
"Squid" wrote in message
news:Po01c.457337$na.1094918@attbi_s04...
I have the following code. For some reason Key2 is not being sorted the
way
I need it. For my santity, I tried switching between ascending/descending
in key2 and both results same. Any ideas why? The sort range is columns
A:AJ. Column B contains numbers and Column D contains dates.


Private Sub Workbook_BeforeClose(Cancel As Boolean)

'sort contract workbook (Sheet1) only if Settlement4b.xls is not open

Dim Wsheet As Worksheet
Set Wsheet = Worksheets("Sheet1") 'Contracts workbook

If Not WorkbookOpen("Settlement4b.xls") Then
With Wsheet
.Select
.Range("A1").Sort _
Key1:=.Range("B1"), Order1:=xlAscending, Header:=xlYes, _
Key2:=.Range("D1"), Order1:=xlDescending, Header:=xlYes
End With
End If


As aways Thanks!
Mike

End Sub

Sort Method
 

You shouldn't have Header in twice - header is for the entire sort area, not
for each key.

Other than that, I can't say. Try turning on the macro recorder and
sorting it manually. See what is recorded.

--
Regards,
Tom Ogilvy


"Squid" wrote in message
news:Po01c.457337$na.1094918@attbi_s04...
I have the following code. For some reason Key2 is not being sorted the
way
I need it. For my santity, I tried switching between ascending/descending
in key2 and both results same. Any ideas why? The sort range is columns
A:AJ. Column B contains numbers and Column D contains dates.


Private Sub Workbook_BeforeClose(Cancel As Boolean)

'sort contract workbook (Sheet1) only if Settlement4b.xls is not open

Dim Wsheet As Worksheet
Set Wsheet = Worksheets("Sheet1") 'Contracts workbook

If Not WorkbookOpen("Settlement4b.xls") Then
With Wsheet
.Select
.Range("A1").Sort _
Key1:=.Range("B1"), Order1:=xlAscending, Header:=xlYes, _
Key2:=.Range("D1"), Order1:=xlDescending, Header:=xlYes
End With
End If


As aways Thanks!
Mike

End Sub

Sort Method
 

Your code is sorting only range A1. You need to sort all the
columns. Change

..Range("A1").Sort _
to
..UsedRange.Sort (...)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Squid" wrote in message
news:Po01c.457337$na.1094918@attbi_s04...
I have the following code. For some reason Key2 is not being
sorted the way
I need it. For my santity, I tried switching between
ascending/descending
in key2 and both results same. Any ideas why? The sort range
is columns
A:AJ. Column B contains numbers and Column D contains dates.


Private Sub Workbook_BeforeClose(Cancel As Boolean)

'sort contract workbook (Sheet1) only if Settlement4b.xls is
not open

Dim Wsheet As Worksheet
Set Wsheet = Worksheets("Sheet1") 'Contracts workbook

If Not WorkbookOpen("Settlement4b.xls") Then
With Wsheet
.Select
.Range("A1").Sort _
Key1:=.Range("B1"), Order1:=xlAscending,
Header:=xlYes, _
Key2:=.Range("D1"), Order1:=xlDescending,
Header:=xlYes
End With
End If


As aways Thanks!
Mike

End Sub