Trouble with sorting in VBA

[anamericangod]

I am trying to finish a macro by sorting a worksheet by the most recent date. The beginning of the macro takes data from one worksheet (ws1) and pastes it to a new worksheet (ws2) along with a time stamp (=IF($B2<>"",IF(A2="",NOW(),A2),"")) in column A. So column A is filled with formulas that only return a date if there is data in column B, or when it is pasted.

What I want is the macro to finish by sorting that worksheet so that the information is displayed by the most recent date (descending date), but since new data is being added, I need run from a1 through column K of the last row.

I've tried several things, but I can't get anything to work. The most recent thing that I've tried was altered from a thread I found in 2003:

Dim LastRow As Integer
'This is the last non empty row
LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.Rows.Count
Worksheets("Catchers - Note History").Range("A1:K" &LastRow & ").Sort
Key1:=Worksheets("Catchers - Note History").Columns("A"), Order1:=xlDescending, Header:=xlYes

But that returns an error message. Can anyone help on this?

 

[shg]

    With Worksheets("Catchers - Note History")
        .Columns("A:K").Sort Key1:=.Cells(1), Order1:=xlDescending, Header:=xlYes
    End With

 

[anamericangod]

So this sort of worked. Sometimes my code works just fine, and sometimes it returns an error at ".Columns("A:AG").Sort Key1:+.Cells(1), Order:=xlDescending, Header:=xlYes. My macro also sometimes makes other weird errors, and again I'm unfortuantley not advanced enough to understand what I am mising. My whole code reads:

Sheets("Pitchers - Note History").Select
'Find the last row of data
FinalRow = Range("B70000").End(xlUp).Row
'Loop through each row
Sheets("Pitchers").Select
Range("A2:AG2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Pitchers - Note History").Select
NextRow = Range("B70000").End(xlUp).Row + 1
Range("B" & NextRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Pitchers - Note History").Select
'Find the last row of data
FinalRow = Range("B70000").End(xlUp).Row
'Loop through each row
Sheets("Pitchers").Select
Range("A6:AG6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Pitchers - Note History").Select
NextRow = Range("B70000").End(xlUp).Row + 1
Range("B" & NextRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Worksheets("Pitchers - Note History")
.Columns("A:AG").Sort Key1:=.Cells(1), Order1:=xlDescending, Header:=xlYes
End With
With Worksheets("Pitchers - Note History")
.Columns("A:AG").Sort Key1:=.Cells(5), Order1:=xlDescending, Header:=xlYes
End With
'Update Catcher Notes
Sheets("Catchers - Note History").Select
'Find the last row of data
FinalRow = Range("B70000").End(xlUp).Row
'Loop through each row
Sheets("Catchers").Select
Range("A2:Y2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Catchers - Note History").Select
NextRow = Range("B70000").End(xlUp).Row + 1
Range("B" & NextRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Worksheets("Catchers - Note History")
.Columns("A:K").Sort Key1:=.Cells(1), Order1:=xlDescending, Header:=xlYes
End With
With Worksheets("Catchers - Note History")
.Columns("A:K").Sort Key1:=.Cells(5), Order1:=xlDescending, Header:=xlYes
End With


And I can't figure this out for the life of me.

 

[anamericangod]

If it helps. 1) It woked at times and 2) if I remove the pitcher portion and just run the catchers (which appears to be the same to me) it works