VBA Sort headers and subtotals

[wilkisa]

I have created the following macro:

Sub SortSubMacroTest()
'
' SortSubMacroTest Macro
' Macro recorded 7/25/2007 by Shirlene
'
Range("K4").Select
Selection.EntireColumn.Delete
Range("A1:B1").Select

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$4"
.PrintTitleColumns = ""
End With

ActiveSheet.PageSetup.PrintArea = ""

With ActiveSheet.PageSetup
.PrintHeadings = False
.PrintGridlines = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With

Range("A4").Select

Call Subtotals

With Selection
.VerticalAlignment = xlBottom
.WrapText = True
End With

Range("A4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Rows.AutoFit

Call PageBreak

Range("A1").Select

End Sub

Sub PageBreak()
Dim RNG As Range
Dim CurrNM As String

CurrNM = Range("A5").Value
For Each RNG In Range("A5", Range("A65536").End(xlUp))
If Not (CurrNM = RNG.Value Or RNG = "") Then
CurrNM = RNG.Value
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=RNG
End If
Next RNG
End Sub

Sub Subtotals()

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("B5") _
, Order2:=xlAscending, Key3:=Range("D5"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7, 8, 11) _
, Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub


The Subtotal routine works just fine if I put it into its own module and F8 through it. However, when I call it from SortSubMacroTest it will sort my header row just as if it was a row of data. In the Sort part of the macro, I changed xlGuess to xlTrue and then it didn't sort the header row but then it would not do the subtotals. It gave me an error that it didn't know which row was the header row!

Help! Anyone?

 

[wilkisa]

No response from anyone yet...has anyone got any ideas?