Dear All,
I am a self taught VBA user and I'm sure my code is inefficient and sloppy but I usually end up getting it to do what I want. I've run into a snag and I'm completely lost. The following macro is a called macro that runs as intended on my machine. When my boss tries to run it (it's a tool for him) he gets the following error: "Object doesn't support this propery or method". I've run it step by step on his machine and I think I've identified where it fails.
(FYI I am using Excel 2016 and he's using Excel 2013)
***UPDATE - I tried it on another coworker's machine with Excel 2016 and it worked find there as well
***UPDATE #2 - It also worked on a coworker's machine with Excel 2019.
Thanks in advance for your assistance.
Sub Sort_JobCosting()
Dim wB As Workbook
Dim pE As Worksheet
Dim cC As Worksheet
Dim cA As Worksheet
Dim cM As Worksheet
Dim rO As Worksheet
Dim jC As Worksheet
Dim x As Integer
Dim y As Integer
Dim z As Double
Dim userResponse As String
Set wB = ThisWorkbook
Set pE = wB.Worksheets("Paste Expense")
Set cC = wB.Worksheets("Control Center")
Set cA = wB.Worksheets("Calculator")
Set cM = wB.Worksheets("CURRENT MONTH")
Set rO = wB.Worksheets("Roster")
Set jC = wB.Worksheets("Job Costing")
x = 4
y = 2
z = 0
jC.Select
Columns("A:I").Select
jC.Sort.SortFields.Clear
'the next line is where it fails
jC.Sort.SortFields.Add2 Key:=Range( _
"E2:E1200"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
jC.Sort.SortFields.Add2 Key:=Range( _
"A2:A1200"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
jC.Sort.SortFields.Add2 Key:=Range( _
"F2:F1200"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With jC.Sort
.SetRange Range("A1:G1200")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Do Until jC.Cells(y, 1) = ""
jmp:
If jC.Cells(y, 1) = jC.Cells(y + 1, 1) And jC.Cells(y, 5) = jC.Cells(y + 1, 5) And jC.Cells(y, 6) = jC.Cells(y + 1, 6) Then
z = z + jC.Cells(y, 4).Value
y = y + 1
GoTo jmp
Else
z = z + jC.Cells(y, 4).Value
Rows(y + 1).Insert shift:=xlDown, _
CopyOrigin:=xlFormatFromLeftOrAbove
jC.Cells(y + 1, 3) = "Total:"
jC.Cells(y + 1, 3).HorizontalAlignment = xlRight
jC.Cells(y + 1, 3).Font.Bold = True
'If z = 0 Then z = jC.Cells(y, 4).Value2
jC.Cells(y + 1, 4) = z
'jC.Cells(y + 1, 4).NumberFormat = "0.00"
jC.Cells(y + 1, 4).Font.Bold = True
y = y + 1
End If
z = 0
y = y + 1
Loop
End Sub
Bookmarks