# Sorting in excel with VBA



## sheyer (Apr 2, 2008)

I'm trying to create a macro that will sort data in multiple worksheets using VBA. This is what I have so far, but I keep running into an error after 'Do'. 
When I put in the name of the worksheet instead of 'i' it works but i'd rather not have to do that for every worksheet since there are a lot of them
Any help?

Sub FixError()

Dim i As Integer

i = 2

Do
Worksheets(i).sort.SortFields.Add Key:=Range("D21000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Worksheets(i).sort.SortFields.Add Key:=Range("E2:E1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Worksheets(i).sort.SortFields.Add Key:=Range("F2:F1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Worksheets(i).sort
.SetRange Range("A1:AZ1000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
i = i + 1
Loop Until i = 17

End Sub


----------



## ShosMeister (Jan 19, 2008)

Sheyer,

Welcome first off. Second, I don't have a copy of Excel handy to test, but, there is no sort method for the Worksheets. Sort only applies to a range.

So, instead of
Worksheets(i).sort.SortFields.Add Key:=Range("D21000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Try something like
Worksheets(i).Range("D21000").sort Key1:=Worksheets(i).Columns("D") SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Again, can't be sure of the exact syntax, but, I'm sure you need the range in the method call.


----------

