Moving data to another sheet based on criteria

Hello,

Please, I would require your assistance if you can help me figure out how I can do this.

I need to move data from one sheet to another based on the following criteria (See attached file)

CDN
To display all Canadian units.
Except fields define as "United States" & "*"


CDN TONNAGE
To display all Canadian Tonnage except “United States”.
Units including values define as “*” have to be included

USA
All USA units defined with “United States”

Empties
Display units without “Final Destination” and “Gross WT Tons”

Ontario
All units defined with “, ON” under final destination

Any help would be greatly appreciated.

Thanks,
Ant

Originally Posted by stanleydgromjr

jantonio,

In your workbook, sheet1, cell A2 = "Toronto, ON"

Should the next three cells A3, A4, and A5 = "Toronto, ON"? Is it implied?

I will have to automatically fill in the empty cells with some macro code.

No, A3, A4, and A5 are local units which are staying locally in Montreal and are not considered part of Toronto.

Thank you...

I came up with this which does the trick:

Please remove sheets (ONTARIO, EMPTIES, USA, CDN TONNAGE, CDN) from original "move data.xls" prior to running macro.

How can I put a timer on this for user to view that macro is running?

Sub AntonioAddRegion()
'
' Macro01 Macro
' Macro recorded 04/05/2009 by Antonio O
'
    Worksheets.Add().Name = "CDN"
    Worksheets.Add().Name = "CND TONNAGE"
    Worksheets.Add().Name = "USA"
    Worksheets.Add().Name = "EMPTIES"
    Worksheets.Add().Name = "ONTARIO"
    
    Dim iAll As Integer
    Sheets("Sheet1").Select
    'iAll = ActiveWorkbook.Sheets(1).UsedRange.Rows.Count
    iAll = Sheets("Sheet1").UsedRange.Rows.Count
    
    Dim I As Integer
    Dim ICDN As Integer
    Dim ITONNAGE As Integer
    Dim IUSA As Integer
    Dim IEMPTIES As Integer
    Dim IONTARIO As Integer
    
    ICDN = 1
    ITONNAGE = 1
    IUSA = 1
    IEMPTIES = 1
    IONTARIO = 1
    
    Dim IFlagUSA As Integer
    Dim IFlagStar As Integer
    Dim IFlagON As Integer
    Dim IFlagEmpty As Integer
    
    I = 1
    Dim TA As String
    Dim TB As String
    Dim TC As String
    Dim TD As String
    Dim TE As String
    'TA = "Final Destination"
    'TB = "Container Number"
    'TC = "*"
    'TD = "Container Type"
    'TE = "Gross WT Tons"
    
   
    For I = 1 To iAll
        'Sheets("Sheet1").Select
        TA = Sheets("Sheet1").Range("A" & I).Value
        TB = Sheets("Sheet1").Range("B" & I).Value
        TC = Sheets("Sheet1").Range("C" & I).Value
        TD = Sheets("Sheet1").Range("D" & I).Value
        TE = Sheets("Sheet1").Range("E" & I).Value
        
        If InStr(TA, "United States") > 0 Then
           IFlagUSA = 1
        Else
           IFlagUSA = 0
        End If
            
        If Trim(TC) = "*" Then
           IFlagStar = 1
        Else
           IFlagStar = 0
        End If
        
        If InStr(TA, ", ON") > 0 Then
           IFlagON = 1
        Else
           IFlagON = 0
        End If
        
        If Trim(TE) = "" Then
           IFlagEmpty = 1
        Else
           IFlagEmpty = 0
        End If
        
        If (I = 1) Or ((IFlagUSA = 0) And (IFlagStar = 0)) Then
            Sheets("CDN").Select
            Range("A" & ICDN).Select
            ActiveCell.FormulaR1C1 = TA
            Range("B" & ICDN).Select
            ActiveCell.FormulaR1C1 = TB
            Range("C" & ICDN).Select
            ActiveCell.FormulaR1C1 = TC
            Range("D" & ICDN).Select
            ActiveCell.FormulaR1C1 = TD
            Range("E" & ICDN).Select
            ActiveCell.FormulaR1C1 = TE
            ICDN = ICDN + 1
        End If
        
        If (I = 1) Or (IFlagUSA = 0) Then
            Sheets("CND TONNAGE").Select
            Range("A" & ITONNAGE).Select
            ActiveCell.FormulaR1C1 = TA
            Range("B" & ITONNAGE).Select
            ActiveCell.FormulaR1C1 = TB
            Range("C" & ITONNAGE).Select
            ActiveCell.FormulaR1C1 = TC
            Range("D" & ITONNAGE).Select
            ActiveCell.FormulaR1C1 = TD
            Range("E" & ITONNAGE).Select
            ActiveCell.FormulaR1C1 = TE
            ITONNAGE = ITONNAGE + 1
        End If
        
        If (I = 1) Or (IFlagUSA = 1) Then
            Sheets("USA").Select
            Range("A" & IUSA).Select
            ActiveCell.FormulaR1C1 = TA
            Range("B" & IUSA).Select
            ActiveCell.FormulaR1C1 = TB
            Range("C" & IUSA).Select
            ActiveCell.FormulaR1C1 = TC
            Range("D" & IUSA).Select
            ActiveCell.FormulaR1C1 = TD
            Range("E" & IUSA).Select
            ActiveCell.FormulaR1C1 = TE
            IUSA = IUSA + 1
        End If
        
        If (I = 1) Or (IFlagEmpty = 1) Then
            Sheets("EMPTIES").Select
            Range("A" & IEMPTIES).Select
            ActiveCell.FormulaR1C1 = TA
            Range("B" & IEMPTIES).Select
            ActiveCell.FormulaR1C1 = TB
            Range("C" & IEMPTIES).Select
            ActiveCell.FormulaR1C1 = TC
            Range("D" & IEMPTIES).Select
            ActiveCell.FormulaR1C1 = TD
            Range("E" & IEMPTIES).Select
            ActiveCell.FormulaR1C1 = TE
            IEMPTIES = IEMPTIES + 1
         End If
        
        If (I = 1) Or ((IFlagON = 1) And IFlagStar = 0) Then
        'If (I = 1) Or (IFlagON = 1) Then
            Sheets("ONTARIO").Select
            Range("A" & IONTARIO).Select
            ActiveCell.FormulaR1C1 = TA
            Range("B" & IONTARIO).Select
            ActiveCell.FormulaR1C1 = TB
            Range("C" & IONTARIO).Select
            ActiveCell.FormulaR1C1 = TC
            Range("D" & IONTARIO).Select
            ActiveCell.FormulaR1C1 = TD
            Range("E" & IONTARIO).Select
            ActiveCell.FormulaR1C1 = TE
            IONTARIO = IONTARIO + 1
         End If
    Next I
    
    Sheets("CDN").Select
    Columns("E:E").Select
    Selection.NumberFormat = "0.00"
    
    Sheets("CND TONNAGE").Select
    Columns("E:E").Select
    Selection.NumberFormat = "0.00"
    
    Sheets("USA").Select
    Columns("E:E").Select
    Selection.NumberFormat = "0.00"
    
    Sheets("EMPTIES").Select
    Columns("E:E").Select
    Selection.NumberFormat = "0.00"
    
    Sheets("ONTARIO").Select
    Columns("E:E").Select
    Selection.NumberFormat = "0.00"
End Sub