PDA

View Full Version : Getting 1004 Error from sorting a range and cannot figure out why.



Person123
07-23-2014, 01:48 PM
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
07-23-2014, 02:35 PM
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
07-24-2014, 08:55 AM
I'm afraid this gives me the same error. Any other ideas as to what could be causing this?

Bob Phillips
07-24-2014, 03:21 PM
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