Sub Wires()
Dim lRow As Long, nrow As Long, i As Long, n As Long
Dim rng As Range, Cell As Range
Dim Arr As Variant
Dim Ws As Worksheet
On Error GoTo Errorcatch
Application.ScreenUpdating = False
Set Ws = ActiveWorkbook.ActiveSheet
Worksheets.Add().Name = "Outgoing Wires": Worksheets.Add().Name = "Incoming Wires"
With Sheets("Incoming Wires")
.Range("B2:I2") = Array("Wire Date", "Amount", "Originator", "Orig. Bank", "Orig. Acct", "Country", "Currency", "Orig. City/State")
.Range("C:C").NumberFormat = "#,##0.00"
End With
With Sheets("Outgoing Wires")
.Range("B2:I2") = Array("Wire Date", "Amount", "Beneficiary", "Ben. Bank", "Ben. Acct", "Country", "Currency", "Ben. City/State")
.Range("C:C").NumberFormat = "#,##0.00"
End With
With Ws
With .UsedRange
.Font.Name = "Calibri"
.Font.Size = 10
For Each Cell In Ws.Range("I:I,H:H,L:L,AN:AN,AU:AU,AY:AY").SpecialCells(xlConstants, xlTextValues)
Cell.Value = WorksheetFunction.Proper(Cell.Value)
Next
lRow = .Rows.Count
End With
For i = 2 To lRow
If .Range("T" & i) = "I" Then
Arr = Array(.Cells(i, 66), .Cells(i, 3), .Cells(i, 51), .Cells(i, 40), .Cells(i, 49), .Cells(i, 16), .Cells(i, 17), .Cells(i, 47))
With Sheets("Incoming Wires")
nrow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
.Cells(nrow, 2).Resize(1, UBound(Arr) + 1) = Arr
End With
ElseIf .Range("T" & i) = "O" Then
Arr = Array(.Cells(i, 66), .Cells(i, 3), .Cells(i, 9), .Cells(i, 8), .Cells(i, 13), .Cells(i, 16), .Cells(i, 17), .Cells(i, 10))
With Sheets("Outgoing Wires")
nrow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
.Cells(nrow, 2).Resize(1, UBound(Arr) + 1) = Arr
End With
End If
With Worksheets("Incoming Wires").UsedRange
.Font.Name = "Calibri"
.Font.Size = 10
.Columns.AutoFit
End With
With Worksheets("Outgoing Wires").UsedRange
.Font.Name = "Calibri"
.Font.Size = 10
.Columns.AutoFit
End With
Next i
End With
Application.ScreenUpdating = True
Exit Sub
Errorcatch:
MsgBox Err.Description
End Sub
Bookmarks