Sorting Problem

[Jim Berglund]

I have name & address data that I want to format. I can't seem to get this
to work. It stops on the .Header statement with an Error 438
What am I missing, now, please?
Jim Berglund

Option Explicit
Sub SortAndColor()
Dim q, i As Long
With ActiveSheet

Columns("A:G").Select
Selection.Columns.AutoFit
q = .Range("A" & Rows.Count).End(xlUp).Row
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("F1:F" & q), SortOn:=xlSortOnValues,
Order:=xlDescending, _
DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=Range("C1:C" & q), SortOn:=xlSortOnValues,
Order:=xlDescending, _
DataOption:=xlSortNormal
.Sort.SetRange Range("A1:F" & q)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

For i = 1 To q Step 2

.Range(Cells(i, 1), Cells(i, 5)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -4.99893185216834E-02
.PatternTintAndShade = 0

Next
.Range("A1:G1").Insert Shift:=xlDown
.Range("A1").FormulaR1C1 = "LAST NAME"
.Range("B1").FormulaR1C1 = "FIRST NAME"
.Range("C1").FormulaR1C1 = "ADDRESS"
.Range("D1").FormulaR1C1 = "CITY"
.Range("E1").FormulaR1C1 = "PROV"
.Range("F1").FormulaR1C1 = "POSTAL CODE"
.Range("G1").FormulaR1C1 = "HOME PHONE"
.Rows("1:1").Select
.Selection.Font.Bold = True

.PageSetup.PrintArea = "$A:$G"

End With
End Sub

 

[Paul Robinson]

Hi
Only a quick look but your With syntax has

Activesheet. Header = xlNo

effectively. I would guess your Header parent should be a range?

regards
Paul

 

[Jim Berglund]

Thanks Paul. I have it working, now.
Jim

Hi
Only a quick look but your With syntax has

Activesheet. Header = xlNo

effectively. I would guess your Header parent should be a range?

regards
Paul