Is there an easier way to write this?

W

Woodyexcel

New Member
Joined
Jun 28, 2011
Messages
8
I am sorting information from one/two cloumn to 10 different sheets, can it be accomplished easier? Thanks in advance.

Woody



Sub MultiSort()
Call BURSort
Call CSTSort
Call DELSort
Call EMPSort
Call LAASort
Call SANSort
Call TMUSort
Call CLSDSort
Call ABYSort
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=4
Range("A1").Select
End Sub

Sub BURSort()
'
' BURSort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*BUR*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("BURBANK").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub CSTSort()
'
' CSTSort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*EMP*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Coast").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub DELSort()
'
' DELSort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*EMP*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Del Rey").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub EMPSort()
'
' EMPSort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*EMP*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("EMPIRE").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub LAASort()
'
' LAASort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*EMP*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("LA Arrivals").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub SANSort()
'
' SANSort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*EMP*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("San Diego").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub TMUSort()
'
' TMUSort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*EMP*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("TMU").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub CLSDSort()
'
' ClosedSort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=5, Criteria1:="=*Closed*", Operator:=xlOr, _
Criteria2:="=*Complete*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Closed-Complete").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub ABYSort()
'
' ABEYANCE Sort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=5, Criteria1:="Abeyance"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Abeyance").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Try this macro, i didn't get to test it but it should be pretty straightforward.

Code: 
Sub SortCopyPaste()

With Sheets("Requests (ALL)").Cells

.AutoFilter 
.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*BUR*"
.SpecialCells(xlCellTypeVisible).Copy
Sheets("BURBANK").Paste
Sheets("BURBANK").Columns("H:I").ClearContents

.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*EMP*"
.SpecialCells(xlCellTypeVisible).Copy
Sheets("Coast").Paste
Sheets("Del Rey").Paste
Sheets("EMPIRE").Paste
Sheets("La Arrivals").Paste
Sheets("San Diego").Paste
Sheets("TMU").Paste
Sheets("Coast").Columns("H:I").ClearContents
Sheets("EMPIRE").Columns("H:I").ClearContents
Sheets("Del Rey").Columns("H:I").ClearContents
Sheets("Las Arrivals").Columns("H:I").ClearContents
Sheets("San Diego").Columns("H:I").ClearContents
Sheets("TMU").Columns("H:I").ClearContents

.AutoFilter Field:=5
.AutoFilter Field:=5, Criteria1:="=*Closed*", Operator:=xlOr, _
Criteria2:="=*Complete*"
.SpecialCells(xlCellTypeVisible).Copy
Sheets("Closed-Complete").Paste
Sheets("Closed-Complete").Columns("H:I").ClearContents


.AutoFilter Field:=5, Criteria1:="Abeyance"
.SpecialCells(xlCellTypeVisible).Copy
Sheets("Abeyance").Paste
Sheets("Abeyance").Columns("H:I").ClearContents

.AutoFilter

End With

End Sub
 
Last edited:
Upvote 0
Thanks so much, I see I put an errored copy of the macro up. This is the correct one


Sub MultiSort()
Call BURSort
Call CSTSort
Call DELSort
Call EMPSort
Call LAASort
Call SANSort
Call TMUSort
Call CLSDSort
Call ABYSort
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Range("A1").Select
End Sub

Sub BURSort()
'
' CSTSort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*BUR*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Burbank").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub CSTSort()
'
' CSTSort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*CST*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Coast").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub DELSort()
'
' DELSort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*DEL*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Del Rey").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub EMPSort()
'
' EMPSort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*EMP*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("EMPIRE").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub LAASort()
'
' LAASort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*LAA*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("LA Arrivals").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub SANSort()
'
' SANSort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*SAN*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("San Diego").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub TMUSort()
'
' CSTSort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
Criteria2:="=*TMU*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("TMU").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub CLSDSort()
'
' ClosedSort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=5, Criteria1:="=*Closed*", Operator:=xlOr, _
Criteria2:="=*Complete*"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Closed-Complete").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:J").Select
Selection.ClearContents
Range("A1").Select
End Sub

Sub ABYSort()
'
' ABEYANCE Sort Macro
'
Sheets("Requests (ALL)").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=5, Criteria1:="Abeyance"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("Abeyance").Select
Range("A1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.ClearContents
Range("A1").Select
End Sub
 
Upvote 0
Try this one on for size then
Code: 
Sub MultiSort()
    Dim First As Variant, Second As Variant, i As Integer
    First = Array("=*BUR*", "=*CST*", "=*DEL*", "=*EMP*", "=*LAA*", "=*SAN*", "=*TMU*", "=*Complete*")
    Second = Array("Burbank", "Coast", "Del Rey", "EMPIRE", "LA Arrivals", "San Diego", "TMU", "Closed-Complete", "Abeyance")
    
    For i = LBound(First()) To UBound(First())
        Sheets("Requests (ALL)").Select
        Selection.AutoFilter Field:=5
        Selection.AutoFilter Field:=4
        Selection.AutoFilter Field:=4, Criteria1:="=*ALL*", Operator:=xlOr, _
        Criteria2:=First(i)
        Range("A1").Select
        Selection.CurrentRegion.Select
        Selection.Copy
        Sheets(Second(i)).Select
        Range("A1").Select
        ActiveSheet.Paste
        Columns("H:I").Select
        Selection.ClearContents
        Range("A1").Select
    Next i
    
    Sheets("Requests (ALL)").Select
    Selection.AutoFilter Field:=4
    Selection.AutoFilter Field:=5
    Range("A1").Select
End Sub
That should do it. No need for extra calls.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Solved
AutoFilter with Dates - Date range, yes; single date, no
Replies
7
Views
374
Alex Blakenburg
A
C
  • Question Question
Workday in VBA
Replies
1
Views
572
Joe4
Joe4
J
  • Solved
Search/Filter a Spreadsheet by User Entered Value
Replies
3
Views
201
jbennett01
J
N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
120
Northern NY Design
N
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
398
willow1985
willow1985

Forum statistics

Threads
1,224,602
Messages
6,179,844
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top