Public Sub CreateIIFRows()
'
' This macro creates IIF escrow payment and late fee invoice transactions
' in a new worksheet from selected data.
' Created by W. E. Huber on 9/19/2006 to make the process on importing
' Habitat for Humanity home owner payments more efficient and accurate.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

Dim EXP As Worksheet
On Error Resume Next

Dim WST As Worksheet
On Error Resume Next

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

ExportFound = False


'Set sSheet to the Active Worksheet

sSheet = ActiveSheet.Name
Set WST = Sheets(sSheet)

Rem MsgBox ("The active sheet is" & sSheet)

If Selection.Rows.Count > 1 Then
    Set Rng = Selection
Else
    MsgBox ("Please select rows and try again!")
End If

'Setup Export worksheet, Delete existing worksheet if it exists

On Error Resume Next
Set EXP = Sheets("Export")
If Err = 0 Then
    Application.DisplayAlerts = False
    Sheets("Export").Delete
    '--ActiveSheet.Delete
    '--ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
End If

' Set EXP = Sheets.Add(Before:=Sheets(1))
Set EXP = Sheets.Add(After:=Sheets(Sheets.Count))

EXP.Name = "Export"

On Error GoTo 0
  
' Fill the Header rows

EXP.[A1] = "!TRNS"
EXP.[B1] = "TRNSID"
EXP.[C1] = "TRNSTYPE"
EXP.[D1] = "DATE"
EXP.[E1] = "ACCNT"
EXP.[F1] = "NAME"
EXP.[G1] = "CLASS"
EXP.[H1] = "AMOUNT"
EXP.[I1] = "DOCNUM"
EXP.[J1] = "MEMO"
EXP.[K1] = "CLEAR"
EXP.[L1] = "TOPRINT"
EXP.[M1] = "ADDR1"
EXP.[N1] = "ADDR2"
EXP.[O1] = "ADDR3"
EXP.[P1] = "DUEDATE"
EXP.[Q1] = "TERMS"
EXP.[R1] = "PAID"
EXP.[A2] = "!SPL"
EXP.[B2] = "SPLID"
EXP.[C2] = "TRNSTYPE"
EXP.[D2] = "DATE"
EXP.[E2] = "ACCNT"
EXP.[F2] = "NAME"
EXP.[G2] = "CLASS"
EXP.[H2] = "AMOUNT"
EXP.[I2] = "DOCNUM"
EXP.[J2] = "MEMO"
EXP.[K2] = "CLEAR"
EXP.[L2] = "QNTY"
EXP.[M2] = "PRICE"
EXP.[N2] = "INVITEM"
EXP.[O2] = "PAYMETH"
EXP.[P2] = "TAXABLE"
EXP.[Q2] = "REIMBEXP"
EXP.[R2] = "EXTRA"
EXP.[A3] = "!ENDTRNS"

N = 4
For r = 1 To Rng.Rows.Count
    irow = Rng.Cells(r, 1).Row
    If WST.Cells(irow, 1) > 0 Then
        If WST.Cells(irow, 7) > 0 Then
            EXP.Cells(N, 1) = "TRNS"
            EXP.Cells(N, 3) = "INVOICE"
            EXP.Cells(N, 4) = WST.Cells(irow, 2)
            EXP.Cells(N, 4).NumberFormat = "mm/dd/yy"
            EXP.Cells(N, 5) = "Mortgages Receivable"
            EXP.Cells(N, 6) = WST.Cells(irow, 4)
            EXP.Cells(N, 7) = "200 Mortg."
            EXP.Cells(N, 8) = WST.Cells(irow, 7)
            EXP.Cells(N, 8).NumberFormat = "0.00"
            EXP.Cells(N, 11) = "N"
            EXP.Cells(N, 12) = "N"
            EXP.Cells(N, 16) = WST.Cells(irow, 2)
            EXP.Cells(N, 16).NumberFormat = "mm/dd/yy"
            EXP.Cells(N, 18) = "N"
Rem            EXP.Cells(N, 21) = WST.Cells(irow, 2)
Rem            EXP.Cells(N, 21).NumberFormat = "mm/dd/yy"
            EXP.Cells(N + 1, 1) = "SPL"
            EXP.Cells(N + 1, 3) = "INVOICE"
            EXP.Cells(N + 1, 4) = WST.Cells(irow, 2)
            EXP.Cells(N + 1, 4).NumberFormat = "mm/dd/yy"
            EXP.Cells(N + 1, 5) = "Late Fees"
Rem            EXP.Cells(N + 1, 6) = WST.Cells(irow, 4)
            EXP.Cells(N + 1, 7) = "200 Mortg."
            EXP.Cells(N + 1, 8) = -WST.Cells(irow, 7)
            EXP.Cells(N + 1, 8).NumberFormat = "0.00"
            EXP.Cells(N + 1, 11) = "N"
            EXP.Cells(N + 1, 14).Formula = "=vlookup(""" & WST.Cells(irow, 4) & """,Homeowner,2)"
            EXP.Cells(N + 1, 16) = "N"
            EXP.Cells(N + 1, 17) = "N"
            EXP.Cells(N + 2, 1) = "ENDTRNS"
            N = N + 3
        End If
        If WST.Cells(irow, 9) > 0 Then
            EXP.Cells(N, 1) = "TRNS"
            EXP.Cells(N, 3) = "INVOICE"
            EXP.Cells(N, 4) = WST.Cells(irow, 2)
            EXP.Cells(N, 4).NumberFormat = "mm/dd/yy"
            EXP.Cells(N, 5) = "Mortgages Receivable"
            EXP.Cells(N, 6) = WST.Cells(irow, 4)
            EXP.Cells(N, 7) = "200 Mortg."
            EXP.Cells(N, 8) = WST.Cells(irow, 9)
            EXP.Cells(N, 8).NumberFormat = "0.00"
            EXP.Cells(N, 11) = "N"
            EXP.Cells(N, 12) = "N"
            EXP.Cells(N, 16) = WST.Cells(irow, 2)
            EXP.Cells(N, 16).NumberFormat = "mm/dd/yy"
            EXP.Cells(N, 18) = "N"
Rem            EXP.Cells(N, 21) = WST.Cells(irow, 2)
Rem            EXP.Cells(N, 21).NumberFormat = "mm/dd/yy"
            EXP.Cells(N + 1, 1) = "SPL"
            EXP.Cells(N + 1, 3) = "INVOICE"
            EXP.Cells(N + 1, 4) = WST.Cells(irow, 2)
            EXP.Cells(N + 1, 4).NumberFormat = "mm/dd/yy"
            EXP.Cells(N + 1, 5) = "Late Fees"
Rem            EXP.Cells(N + 1, 6) = WST.Cells(irow, 4)
            EXP.Cells(N + 1, 7) = "200 Mortg."
            EXP.Cells(N + 1, 8) = -WST.Cells(irow, 9)
            EXP.Cells(N + 1, 8).NumberFormat = "0.00"
            EXP.Cells(N + 1, 11) = "N"
            EXP.Cells(N + 1, 14) = "FinCharge"
            EXP.Cells(N + 1, 16) = "N"
            EXP.Cells(N + 1, 17) = "N"
            EXP.Cells(N + 2, 1) = "ENDTRNS"
            N = N + 3
        End If
    End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


End Sub