Sort code doesn't work on first pass, works on second

S

standard_guy

Good morning,

I have a daily file where the user goes and updates some queries from an
AS400, then runs my macro in a report speadsheet to format the new day's
info. In the course of the macro, I do the following sort. On a new day,
the sort from what I can tell doesn't work, but then if you run it again it
works like I want it to work. Is there something I need to reset when I
start to make the sort work the first time? I do a lot of other things after
the sort and somewhere in there I must do what I need to do but I haven't
been able to figure out what it is. (I tried it with and without clearing
the sort fields first and that doesn't matter.

Thanks.

Columns("A:p").Select
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add
Key:=Range("P2:p1000" _
), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add
Key:=Range("I2:I1000" _
), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add
Key:=Range("B2:B1000" _
), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange Range("A1:p1000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
 
S

standard_guy

Well I revisited this and figured it out so since I use other people's
answered questions a lo I figured I'd add what I found in case it helps
someone else.

Somehow I had left some subtotals on in the area where I was dropping in the
new data for the new day. Somehow the first time my macro ran the subtotals
weren't removed, but then the next time I ran it they were. I moved the line
clearing the subtotals down a bit in the code and fixed it. Upshot - If you
sort works intermittently chec whether you have any subtotalling going on.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top