Problem with my VB code

[vmottert]

This is for an inventory workbook. The sheet "SOA" contains many rows of inventory. The sheet "SOA_2" is an extraction of only those inventory rows on SOA that have units ordered (Units>0).

Both SOA and SOA_2 have list boxes on them that will sort the inventory 4 ways. The list boxes punch the numbers 1, 2, 3 or 4 into cell 'SOA!Y4" and then run a macro that does the sort. This all works fine except for the list box on SOA_2 (code below).

When a selection is made on the list box on SOA_2, it runs the macro below - Sub Extract_SOA2_Data(). As shown, that macro makes SOA the active sheet and goes to the macro Sub Sort_Inventory() (aslo below) to sort SOA the desired way. Then it should return to Sub Extract_SOA2_Data() and extract the ordered inventory wiht the desired sort.

When I step through it (F8) everything works fine through sorting on SOA. But I get "Run-Time error '1004' Application defined or object-defined error." and the line "Sheet2.Sort_Inventory" is highlighted in yellow.

Hope someone can spot where I have gone wrong ... (code below)


Sub Extract_SOA2_Data()

Application.ScreenUpdating = False
Sheets("SOA").Select
Sheet2.Sort_Inventory

Sheets("SOA_2").Select

Sheets("SOA").Range("Database").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("SOA_2!Y5:Y6"), CopyToRange:=Range("SOA_2!A12:W12"), _
Unique:=False

End Sub


Sub Sort_Inventory() 'Sort Inventory by Station, Daypart, CPP

Temp = Range("SOA!X8")

LastRow = Range("B" & Rows.Count).End(xlUp).Row
Set rng = Range("B12:T" & LastRow)

Range("SOA!X8").Value = 1 ' Change to CPP because CPMs don't sort right

If Range("SOA!Y4").Value = 1 Then ' Sort on Station, Daypart and CPP
rng.Sort Key1:=Range("B13"), Order1:=xlAscending, Key2:=Range("D13" _
), Order2:=xlAscending, Key3:=Range("G13"), Order3:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Else
End If

If Range("SOA!Y4").Value = 2 Then ' Sort on Daypart and CPP
rng.Sort Key1:=Range("D13"), Order1:=xlAscending, Key2:=Range("C13" _
), Order2:=xlAscending, Key3:=Range("G13"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Else
End If

If Range("SOA!Y4").Value = 3 Then ' Sort on GRPs H-L and CPP
rng.Sort Key1:=Range("E13"), Order1:=xlDescending, Key2:=Range( _
"G13"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom
Else
End If

If Range("SOA!Y4").Value = 4 Then ' Sort on GRPs H-L and CPP
Range("B12").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("G13"), Order1:=xlAscending, Key2:=Range("E13" _
), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Else
End If

If Range("SOA!Y4").Value = 5 Then ' Sorts all to the bottom so avails may be added to the top
Range("A12:X312").Select
Selection.Sort Key1:=Range("U13"), Order1:=xlAscending, Key2:=Range("A13" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom
Else
End If

Range("SOA!X8") = Temp 'Resets Cost Comparison.

End Sub

 

[vmottert]

Never Mind

I found it. One of my list boxes was still linked to code in another worksheet. Boy do I feel dumb!

Regards,

Vernon