Sub AirFreightReferrals()

'Jacek Jarecki Air Freight Referrals'
'tested on vba 2007 excell'

'set variables
Dim myColumns As Variant
Dim myStyles As Variant
Dim style As Variant
Dim x As Variant
Dim y As Variant
Dim findfield As Variant
Dim currentColumn As Integer
Dim columnHeading As String
Dim oCell As Range
Dim iNum As Long
Dim iRow As Integer
Dim column As Integer


'*********************************************************************************************************************
'*********************************************************************************************************************
'type columns you want(using headers) in order you like

myColumns = Array("customer_code", "invoice_number", "dealer_pick_pack_code", "part_description", "finis_code", "pieces_shipped")  '<---YOU CAN EDIT remember to change columns for sorting (STEP THREE)

myStyles = Array("default", "default", "default", "style1", "style2", "style3")                                                    '<---YOU CAN EDIT

'*********************************************************************************************************************
'*********************************************************************************************************************

'STEP ONE:_______________KEEP YOUR COLUMNS AND REARRANGE

For x = LBound(myColumns) To UBound(myColumns) ' from the lowest index in array to the highest

    findfield = myColumns(x)
    
    iNum = iNum + 1
    Set oCell = ActiveSheet.Rows(1).Find(What:=findfield, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
    If Not oCell.column = iNum Then
        Columns(oCell.column).Cut
        Columns(iNum).Insert Shift:=xlToRight
    End If
    
   
        
Next x




'STEP TWO:_______________DELETE THE REST of COLUMNS

For currentColumn = ActiveSheet.UsedRange.Columns.Count To (UBound(myColumns) + 2) Step -1      'UsedRange - how many columns in use
   
        ActiveSheet.Columns(currentColumn).Delete
       
Next currentColumn

'STEP THREE:_______________SORT COLUMNS C and E ( !!! if you rearrange change colums to sort as well)

ActiveSheet.sort.SortFields.Clear
ActiveSheet.sort.SortFields.Add Key:=Range("C2:C9999"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.sort.SortFields.Add Key:=Range("E2:E9999"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.sort

    .SetRange Range("A1:Z999")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    
End With

    
'STEP FOUR:_______________MAKE SPACES BETWEEN COUNTRIES


Range("a1").Select
iRow = 1

Do

    If Cells(iRow + 1, 1) <> Cells(iRow, 1) Then
    
        'insert row'
        Cells(iRow + 1, 1).EntireRow.Insert Shift:=xlDown
        'set the height of row
        Cells(iRow + 1, 1).Range("A1").RowHeight = 4
        
        
        'create background colour in all columns (using loop)'
        Dim index As Integer
        index = 1
        
        Do While index <= UBound(myColumns) + 1
        
            Cells(iRow + 1, index).Interior.ColorIndex = 15
            index = index + 1
        
        Loop
                               
        iRow = iRow + 2
        
    Else
    
        iRow = iRow + 1
        
    End If

Loop While Not Cells(iRow, 1).Text = ""

'STEP FIVE:_______________MAKE Styles

For y = LBound(myColumns) To UBound(myColumns) ' from the lowest index in array to the highest
    
    style = myStyles(y)
    column = y + 1
    Select Case style
        
            Case "style1"
             
            Columns(column).HorizontalAlignment = xlHAlignRight
            Columns(column).ColumnWidth = 35
               
            Case "style2"
            
               Columns(column).ColumnWidth = 12
               Columns(column).HorizontalAlignment = xlCenter
               
            Case "style3"
            
               Columns(column).HorizontalAlignment = xlHAlignLeft
               
            Case Else
            
               ' do nothing
               Columns(column).HorizontalAlignment = xlCenter
               
       End Select

Next y

End Sub