Runtime Error 438

[tlc53]

Hi there,
I have the following VBA code which works fine on my system but when someone else opens it on their computer, it's returning "Runtime Error 438".
Since it's not showing me the error, it's difficult to narrow in on. Is there anything obvious that may be causing it?
Thank you!

Sub Prepare()
'
' Prepare Macro

'Unprotect sheet
    Sheets("Stocktake Print Sheets").Select
    ActiveSheet.Unprotect

'Select table "Member_Table_Subtotal"
    Application.Goto Reference:="Member_Table_Subtotal"
    
'Subtotal = Remove all
    Selection.RemoveSubtotal
    
'Remove filters
    Selection.AutoFilter
    
'Select table "Member_Table"
    Application.Goto Reference:="Member_Table"
    
'Sort table by "Team Member" then by "Location"
    ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort.SortFields.Add2 Key _
        :=Range("D6:D122"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort.SortFields.Add2 Key _
        :=Range("C6:C122"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort
        .SetRange Range("A5:G122")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
'Add filter, Under title "Location" hide "Blank"
    Selection.AutoFilter
    Range("$A$5:$G$122").AutoFilter Field:=3, Criteria1:="<>Blank"
 
 'Add subtotals
    Selection.Subtotal GroupBy:=4, Function:=xlCount, TotalList:=Array(1), _
        Replace:=False, PageBreaks:=True, SummaryBelowData:=True
    ActiveWindow.SmallScroll Down:=-24
    
 'Go to top of table
    Range("A5").Select
 
 'Protect Sheet
    Sheets("Stocktake Print Sheets").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[Domenic]

In your code, you reference Selection a number of times. And, it looks like for each one of those selections, it expects a Range object to be selected. So, if an object other than a Range object is selected when the code runs, you'll likely get that error message.

 

[tlc53]

In your code, you reference Selection a number of times. And, it looks like for each one of those selections, it expects a Range object to be selected. So, if an object other than a Range object is selected when the code runs, you'll likely get that error message.

Any idea how I should amend the code to avoid this? I'm not quite sure how to fix it.

 

[tlc53]

When debug is selected, this is the part of the code that is highlighted. How can I amend this code so it doesn't return Runtime Error 438?
Thank you!

 ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort.SortFields.Add2 Key _
        :=Range("D6:D122"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal

 

[Domenic]

The syntax seems correct. Which version of Excel are you using?

 

[tlc53]

The syntax seems correct. Which version of Excel are you using?

The problem I have, is that it's working fine on my system. No errors are occurring.
It's when I email it through and possibly, at their end, they're working on an old version of Excel. I've asked what version they are using and I'm waiting for a reply. They sent me a screenshot of the highlighted code when debugged (as below).
I tried selecting the "Member Table" before the error line, but that didn't fix it. Is there anything else I could try?

Sub Prepare()
'
' Prepare Macro

'Unprotect sheet
    Sheets("Stocktake Print Sheets").Select
    ActiveSheet.Unprotect

'Select table "Member_Table_Subtotal"
    Application.Goto Reference:="Member_Table_Subtotal"
  
'Subtotal = Remove all
    Selection.RemoveSubtotal
  
'Remove filters
    Selection.AutoFilter
  
'Select table "Member_Table"
    Application.Goto Reference:="Member_Table"
  
'Sort table by "Team Member" then by "Location"
    ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort.SortFields.Clear
        Application.Goto Reference:="Member_Table"
 
 'ERROR LINE STARTS HERE AND HIGHLIGHTS 3 LINES BELOW
ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort.SortFields.Add2 Key _
        :=Range("D6:D122"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort.SortFields.Add2 Key _
        :=Range("C6:C122"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort
        .SetRange Range("A5:G122")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
  
'Add filter, Under title "Location" hide "Blank"
    Selection.AutoFilter
    Range("$A$5:$G$122").AutoFilter Field:=3, Criteria1:="<>Blank"
 
 'Add subtotals
    Selection.Subtotal GroupBy:=4, Function:=xlCount, TotalList:=Array(1), _
        Replace:=False, PageBreaks:=True, SummaryBelowData:=True
    ActiveWindow.SmallScroll Down:=-24
  
 'Go to top of table
    Range("A5").Select
 
 'Protect Sheet
    Sheets("Stocktake Print Sheets").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

 

[Domenic]

I think this method of sort was introduced in Excel 2007. So, if an earlier version of Excel is being used, you should use the Sort method of the Range object...

Range.Sort method (Excel)

Office VBA reference topic

docs.microsoft.com

 

[tlc53]

I think this method of sort was introduced in Excel 2007. So, if an earlier version of Excel is being used, you should use the Sort method of the Range object...

Range.Sort method (Excel)

Office VBA reference topic

docs.microsoft.com

Thank you! I dug out my old laptop which works on an old version of Excel and I was able to debug and re-work the code accordingly