PDA

View Full Version : Macro only sorts 1 column



mzsuga
07-17-2009, 07:10 AM
I have the following:

Worksheets("Package Summaries").Range("a5", Cells(Rows.Count, "a").End(xlUp)).Sort Key1:=Worksheets("Summary Page").Range("a5"), Order1:=xlAscending, Header:=xlYes
Range("a5").Subtotal GroupBy:=1, Function:=xlSum, totallist:=Array(4, 6, 12, 17, 19), Replace:=True, PageBreaks:=False, _
SummaryBelowData:=True

It onyl sorts column A as oppose to the entire sheet. My headings start at row 5. So I want it to sort by column A then it will subtotal column A. Help please

mdmackillop
07-17-2009, 07:20 AM
Try

With Worksheets("Package Summaries")
.Range("a5", Cells(Rows.Count, "a").End(xlUp)).Resize(, .UsedRange.Columns.Count).Sort Key1:=Worksheets("Summary Page").Range("a5"), Order1:=xlAscending, Header:=xlYes
Range("a5").Subtotal GroupBy:=1, Function:=xlSum, totallist:=Array(4, 6, 12, 17, 19), Replace:=True, PageBreaks:=False, _
SummaryBelowData:=True
End With

Bob Phillips
07-17-2009, 07:25 AM
Malcolm, don't you mean



With Worksheets("Package Summaries")
.Range("a5", .Cells(.Rows.Count, "a").End(xlUp)).Resize(, .UsedRange.Columns.Count).Sort Key1:=Worksheets("Summary Page").Range("a5"), Order1:=xlAscending, Header:=xlYes
Range("a5").Subtotal GroupBy:=1, Function:=xlSum, totallist:=Array(4, 6, 12, 17, 19), Replace:=True, PageBreaks:=False, _
SummaryBelowData:=True
End With


but how do you sort a sheet by a key on another sheet?

mzsuga
07-17-2009, 07:39 AM
Malcolm, don't you mean



With Worksheets("Package Summaries")
.Range("a5", .Cells(.Rows.Count, "a").End(xlUp)).Resize(, .UsedRange.Columns.Count).Sort Key1:=Worksheets("Summary Page").Range("a5"), Order1:=xlAscending, Header:=xlYes
Range("a5").Subtotal GroupBy:=1, Function:=xlSum, totallist:=Array(4, 6, 12, 17, 19), Replace:=True, PageBreaks:=False, _
SummaryBelowData:=True
End With

but how do you sort a sheet by a key on another sheet?

Lol yeah its actualyl the same sheet but that worked perfectly. Thank you so much. But howcome the original macro wasnt working?

Bob Phillips
07-17-2009, 07:51 AM
Because you didn't extend the columns. Malcolm's code addressed that, mine was to ensure that all ranges addressed were on the same sheet.