You had another error, but it wasn't at the bottom. Excel's VBA was looking for
an "End if" in the top portion of your code and didn't find it by the time the
code ended with that "end sub" line. So it yelled near the bottom.

This worked for me:

Option Explicit
Sub testme()

Dim myWS As Worksheet
Dim SortRange As Range
Dim LastRow As Long '<-- Added

Set myWS = Nothing
On Error Resume Next
Set myWS = Sheets("Policy Info")
On Error GoTo 0
If Not myWS Is Nothing Then

LastRow = myWS.Cells(myWS.Rows.Count, "A").End(xlUp).Row
Set SortRange = myWS.Range("A6:O" & LastRow)
SortRange.Sort Key1:=myWS.Range("A6"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If '<-- Added

Set myWS = Nothing
On Error Resume Next
Set myWS = Sheets("Corn Yields")
On Error GoTo 0
If Not myWS Is Nothing Then
LastRow = myWS.Cells(myWS.Rows.Count, "A").End(xlUp).Row
Set SortRange = myWS.Range("A6:P" & LastRow)
SortRange.Sort Key1:=myWS.Range("A6"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If

End Sub


Tail Wind wrote:

Thank you Dave.

I changed the two lines as per you suggestion.

The code still errors out at the end. What should the final lines read? It
doesn't like:
End If

nor does it like:
End Sub

nor does it like:
End If
End Sub

I have no idea. Suggestions?

Thanks again.
End Sub
"Dave Peterson" wrote:

Module3 (no space) is most likely a General module.

My suggestion was to change two lines that looked like:
SortRange.Sort Key1:=Range("A6"), _

by adding "myWs." so that you end up with:
SortRange.Sort Key1:=myWS.Range("A6"), _


Tail Wind wrote:

I looked closer at the VBA screen. The code is being stored with the
workbook containing the worksheets to be sorted. It appears to be being
stored, for reasons totally unknown to me, in Module 3.

HTH

"Tail Wind" wrote:

Thank you Dave for your reply.

The macro associated with all my grief appears to be "General" (upper left
corner drop down box in VBA mode). I really do not know the difference as
you attempted to explain it.

What should the final two lines read? I know hardly anything about VBA.

Your help, as is all of the other's, is most appreciated.

"Dave Peterson" wrote:

Without looking very closely.

Make sure you qualify the ranges that are used for the keys:

SortRange.Sort Key1:=myWS.Range("A6"), _

Depending on where the code is, the unqualified range (range("A6")) will either
refer to the activesheet (in a General module) or the sheet that owns the code
(in a worksheet module).



Tail Wind wrote:

My code now error out on the last line. When I use just End If, the error
message is Expected End Sub. When I add End Sub after the End If, I get
Compile error: Block If without End If. Stumped again. Any help is greatly
appreciated! Code as currently written follows:


Dim myWS As Worksheet
Dim SortRange As Range

Set myWS = Nothing
On Error Resume Next
Set myWS = Sheets("Policy Info")
On Error GoTo 0
If Not myWS Is Nothing Then

LastRow = myWS.Cells(myWS.Rows.Count, "A").End(xlUp).Row
Set SortRange = myWS.Range("A6:O" & LastRow)
SortRange.Sort Key1:=Range("A6"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Set myWS = Nothing
On Error Resume Next
Set myWS = Sheets("Corn Yields")
On Error GoTo 0
If Not myWS Is Nothing Then

LastRow = myWS.Cells(myWS.Rows.Count, "A").End(xlUp).Row
Set SortRange = myWS.Range("A6:P" & LastRow)
SortRange.Sort Key1:=Range("A6"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
End Sub

"Tail Wind" wrote:

I have a macro which is to sort the names of two worksheets. Worksheet
"Policy Info" sorts correctly however worksheet "Corn Yields" (sort colunn A
which are names, test data range is A6: P12) does not. Columns C thru P
(data fields) are not sorted. Column A is used for row count and cell A13
yields a FALSE when =ISNUMBER is used to check the contents. My code
follows. I would be most grateful to someone who could debug it as I am,
frankly, out of my league here.

Thanks in advance....


Sheets("Policy Info").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set SortRange = Range("A6:O" & LastRow)
SortRange.Sort Key1:=Range("A6"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Corn Yields").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set SortRange = Range("A6:P" & LastRow)
SortRange.Sort Key1:=Range("A6"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Policy Info").Select
End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson