0

I am setting up a one touch solution via a form control to sort subtotal, copy and paste information from one workbook to another for multiple users.

Whenever I use my form control it works like a dream, but once a different user saves the files exactly as named and attempts to run the code through the form control I receive an error. The error is being thrown from the last statement in the code below. x.Sheets("APHub").Sort.SortFields.Add2 Key:=Range( _"C2:C42524"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal

The error they are receiving is a 438 run-time stating “the object doesn’t support this property or method”

I have tried defining the worksheet as an object but it didn't correct it. 

Dim x As Workbook
Dim y As Workbook
xName = "Missing_Merch.xlsx"
xNamePath = ThisWorkbook.Path & "\" & xName            
Set x = Workbooks(xName)

Cells.Select
x.Sheets("APHub").Activate
Cells.Select
Cells.EntireColumn.AutoFit
Columns("F:I").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Cells.Select
Selection.AutoFilter
x.Sheets("APHub").Sort.SortFields.Clear
x.Sheets("APHub").Sort.SortFields.Add2 Key:=Range( _
    "C2:C42524"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal

This should start off my sorting sequence before any necessary subtotaling takes place.

Rsheale
  • 11
  • 2
  • 4
    Welcome to SO! [Edit]ing your post to include the specific error and which specific instruction is throwing it, would probably help answerers help you (see [mcve] for more info). That said, I'd recommend reading [this post about avoiding Select and Activate](https://stackoverflow.com/q/10714251/1188513); working with the `Selection` tends to make the code more frail and bug-prone in general. – Mathieu Guindon Mar 31 '19 at 03:46
  • I appreciate the bit on selection and normally try to avoid it however I have used recording to capture most of this and am unsure on how I could replace all of these selections with something more stable. Any thoughts? – Rsheale Apr 01 '19 at 02:32

0 Answers0