Creating a macro to sort a complex dynamic named reference

[Dan]

Hi,
I'm having trouble creating a macro that will sort a named reference. I have
data in Columns A-D that will constantly be adding rows at the bottom. I have
4 header rows at the top so the data does not start until A5. I have created
a named reference called "Totals" which looks like this:
"=OFFSET(Totals!$A$5,0,0,COUNTA(Totals!$A:$A)-3,4)"

I then tried the code below to sort it but am getting an error: "The sort
reference is not valid. Make sure that it's within the data you want to sort,
and the first Sort By blow isn't the same or blank."

Application.Goto Reference:="Totals"
Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("C5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

I've tried searching but so far haven't found any solutions for using named
references instead of defined blocks of cells for this problem.

Any help would be appreciated!

Thank you,
-Dan

 

[Jim Thomlinson]

Here is how I would do it...

Dim rngToSort As Range
Set rngToSort = ThisWorkbook.Names("Totals").RefersToRange

With rngToSort
.Sort Key1:=.Parent.Range("A5"), order1:=xlAscending, _
Key2:=.Parent.Range("C5"), order1:=xlAscending, _
Header:=xlNo
End With

 

[Dan]

Hi Jim,

Unfortunately I still get a runtime 1004 - "Application-defined or
object-defined error". My code looks like this:

Sub SortTotals()

Dim rngToSort As Range
Set rngToSort = ThisWorkbook.Names("Totals").RefersToRange

With rngToSort
..Sort Key1:=.Parent.Range("A5"), order1:=xlAscending, _
Key2:=.Parent.Range("C5"), order1:=xlAscending, _
Header:=xlNo
End With

End Sub

Any ideas on what might be causing this?
-Dan

 

[paulgu]

The double dot in:
..Sort Key1:=.Parent.Range("A5")
looks like a possible culprit to me.