Little extra Info. The below code works if I refer to the cells that the headers are in by their location. However, my spreadsheet doesn't always look the same so I am trying to find those columns by name.
Dim sht As Worksheet
Dim FYr As Range, Instr As Range
Set sht = ActiveSheet
Cells.Interior.ColorIndex = 0 'Set all Colors to blank
'Sort on FY and Installation for OM
Set FY = ActiveSheet.Rows(1).Find("FY")
Set FYr = ActiveSheet.Range(FY.Offset(1, 0), FY.End(xlDown))
Set Inst = ActiveSheet.Rows(1).Find("Installation")
Set Instr = ActiveSheet.Range(FY.Offset(1, 0), FY.End(xlDown))
ActiveSheet.Rows(1).AutoFilter
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.AutoFilter.Sort.SortFields.Clear
ActiveSheet.AutoFilter.Sort.SortFields.Add Key:=Range("F1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
When I try and put my range names instead of "A1" and "F1" it gives me the error.
Bookmarks