Getting 1004 Error from sorting a range and cannot figure out why.

[Person123]

I am working on automating the input of a new category to a list on multiple worksheets. Everything works fine for inserting it into the list but when I try to sort it to make it alphabetical, I get a 1004 Error. This code was working the other day, but for some reason after writing another macro that would add in new employee names automatically (to the other axis of my worksheets), this doesn't work.

What is even stranger is that the categories can sort themselves on some worksheets but not others, when basically nothing is different. Here is the section that sorts on one worksheet:

Sheets("Requirements").Select
Sheets("Requirements").Sort.SortFields.Add Key:=Range("B2:ZZ2"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Requirements").Sort
    .SetRange Range("B2:ZZ5")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlLeftToRight
    .SortMethod = xlPinYin
    .Apply
End With

This one works perfectly fine, but when I try to run the very next part:


Sheets("Employee History").Select
Sheets("Employee History").Sort.SortFields.Add Key:=Range("C1:ZZ1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Employee History").Sort
    .SetRange Range("C1:ZZ500")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlLeftToRight
    .SortMethod = xlPinYin
    .Apply
End With

It won't go through, and I keep ending up with an error. I have tried using smaller ranges and using a range with a loop that tracks the exact location of the last row and column with data. Nothing seems to work, and I have no idea what the problem could be. Any help is greatly appreciated!!!

[Bob Phillips]

With Sheets("Employee History")

    .Sort.SortFields.Add Key:=.Range("C1:ZZ1"), _
                         SortOn:=xlSortOnValues, _
                         Order:=xlAscending, _
                         DataOption:=xlSortNormal
    With .Sort
        .SetRange Range("C1:ZZ500")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
end with

[Person123]

I'm afraid this gives me the same error. Any other ideas as to what could be causing this?

[Bob Phillips]

Missed a dot

With Sheets("Employee History") 
     
    .Sort.SortFields.Add Key:=.Range("C1:ZZ1"), _ 
    SortOn:=xlSortOnValues, _ 
    Order:=xlAscending, _ 
    DataOption:=xlSortNormal 
    With .Sort 
        .SetRange .Range("C1:ZZ500") 
        .Header = xlYes 
        .MatchCase = False 
        .Orientation = xlLeftToRight 
        .SortMethod = xlPinYin 
        .Apply 
    End With 
End With