Select Method of Range Class Failed with Table

[NorthbyNorthwest]

Hi, everyone. I have a macro that sorts a table by employee names. Because each name is first last and share a cell, I have a second macro that flips the names to last first before alphabetizing them and flips them back afterwards. Everything worked just fine until recently when the flip name macro began triggering runtime error "Select method of range class failed." Can someone help?

Sub AlphaZulu()
Dim oSheetName As Worksheet
Dim sTableName As String
Dim loTable As ListObject
Dim rRange1 As Range

'Alphabetize table
Application.EnableEvents = False
Application.ScreenUpdating = False
Sheets("Tables").Activate
Call FlipName4Sort
   
'Define Variable
sTableName = "tblEmployee"
   
'Define WorkSheet object
Set oSheetName = ActiveSheet
   
'Define Table Range
Set rRange1 = Range("tblEmployee[Employee]")
   
'Define Table Object
Set loTable = oSheetName.ListObjects(sTableName)
   
    'Sort Single Column Table
    With loTable.Sort
        'specify header is available or not
        .Header = xlYes
       
        'Clear if any existing sort
        .SortFields.Clear
       
        'Specify Column to sort
        .SortFields.Add Key:=rRange1, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        'sort specified column in a table
        .Apply
    End With

Call FlipName4Sort
Sheet9.Range("J5").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sub FlipName4Sort()

'Reverse names in column B of Employee table
Application.EnableEvents = False
Range("tblEmployee[Employee]").Select

On Error Resume Next
iRows = Selection.Rows.Count
Set lastcell = Cells.SpecialCells(xlLastCell)
mrow = lastcell.Row
If iRows > mrow Then iRows = mrow
imax = -1
For ir = 1 To iRows
checkx = Trim(Selection.Item(ir, 1))
L = Len(Trim(Selection.Item(ir, 1)))
If L < 3 Then GoTo nextrow
For im = 2 To L
If Mid(checkx, im, 1) = "," Then GoTo nextrow
If Mid(checkx, im, 1) = " " Then imax = im
Next im
If imax > 0 Then
Selection.Item(ir, 1) = Trim(Mid(checkx, _
imax, L - imax + 1)) & " " & _
Trim(Left(checkx, imax))
End If
nextrow:
Next ir
terminated:
Application.EnableEvents = False
End Sub

 

[StephenCrump]

You didn't say which line produced the error, but I'm guessing:

Sheet9.Range("J5").Select

If Sheet9 is not the ActiveSheet, you'll need two steps: activate the sheet and then select the range.

 

[NorthbyNorthwest]

You didn't say which line produced the error, but I'm guessing:

Sheet9.Range("J5").Select

If Sheet9 is not the ActiveSheet, you'll need two steps: activate the sheet and then select the range.

 

[NorthbyNorthwest]

Actually, it was the second row of the FlipName4Sort. Initially, it was no problem. But then it began giving me the runtime error. I tried solving problem two ways. First, I tried adding a second select line of code:

ThisWorkbook.Worksheets("Tables"). Select

Range("tblEmployee[Employee]").Select

This worked. I know that over reliance on select is frowned upon in coding. So, I tried substituting the "rRange1" variable for "selection" throughout the code as a way of avoiding select. It did not work. I wound up with a jumbled-up sort, not an alphabetical sort. So, I'm at a loss as to what else I can do. I'm concerned the double use of select may go off the rails at some point.

 

[NorthbyNorthwest]

Thanks for responding Stephen. I changed the two select statements to an activate and select statement as you advised.