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!!!
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!!!