Using sheet codename in sorting code

Question

I've been trying to write all my code using codenames to prevent errors if a name gets changed and to stop activating/selecting sheets. This was going well until I got to the sort code where I thought the following would select Sheet2

ActiveWorkbook.Sheets(2).Sort.SortFields.Clear
ActiveWorkbook.Sheets(2).Sort.SortFields.Add Key:=Range( _
  "A2:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  xlSortNormal
ActiveWorkbook.Sheets(2).Sort.SortFields.Add Key:=Range( _
  "J2:J" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  xlSortNormal
With ActiveWorkbook.Sheets(2).Sort
  .SetRange Range("A1:J" & LastRow)
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With

It does in fact work on Sheet2, but I found out after adding a new sheet that it is counting sheets based on their position in the document (tabs at the bottom).

Trying to replace any of those lines with something like ActiveWorkbook.Sheet2.Sort.SortFields.Clear gives me error "438 - Object doesn't support this property or method".

The only way I have been able to get this to work is to replace it with ActiveWorkbook.Sheets(Sheet2.Name).Sort.SortFields.Clear. It seems to me that maybe I am missing something here but I am having trouble searching for anything related to codename and sorting.

is the code in the same workbook? To quote OzGrid, "The only times you cannot use a sheets CodeName is when you reference a Sheet that is in a different Workbook to the one that the code resides." ozgrid.com/VBA/excel-vba-sheet-names.htm — Hrothgar

Scott Craner Scott Craner · Accepted Answer · 2016-10-21T17:06:50

Just assign the sheet to a variable using the Code Name:

Dim ws As Worksheet
Set ws = Sheet2

Then you would call each line with:

ws.Sort...

Using sheet codename in sorting code

2 Answers