VBA code to copy line above with changes.

[shophoney]

Hello,

I'm looking for some help on VBA. The code is to create a file to import into QuickBooks for invoices.

I have some code i've written that takes a pivottable and copies it to a new tab. Pastes it as values and insets a blank line.

I would like to add another line that is the "SPL" that is the same as the line above but with a negative value adding "Owners Draw" to the ACCOUNT.

Example below adds line 5. A5= SPL. E5= Owners Draw. G5=-G4. Add line 6, A6 = ENDTRNS.

Sub Quickbooks_Import()
Dim ws As Worksheet
Dim rng As Range

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Set ws = Worksheets.Add(after:=Sheets("QB Import"))
ws.Name = "QB iif " & Format(Date, "mm_dd_yy") & "@" & Format(Time, "hhmm")


Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Application.GoTo Reference:="R1C1"
Rows("3:3").Select
Selection.Delete Shift:=xlUp

Call InsertOwnersDraw
Call InsertENDTRNS

ChDir "D:\Dropbox\FaceUp Innovations Consulting Inc\Import"
ActiveWorkbook.SaveAs Filename:= _
"D:\Dropbox\FaceUp Innovations Consulting Inc\Import\Export.iif" & Format(Date, "mm_dd_yy") & "@" & Format(Time, "hhmm"), FileFormat:=xlText, _
CreateBackup:=False
End Sub

Sub InsertOwnersDraw()

Dim export As Worksheet
Dim UnnecessaryRow As Range
Dim TRNS_ROW As Range

Set TRNS_ROW = Range("A4")

While TRNS_ROW.Value <> ""

If TRNS_ROW.Value = "TRNS" And TRNS_ROW.Offset(-1, 0) <> "SPL" Then

TRNS_ROW.EntireRow.Insert

TRNS_ROW.Offset(-1, 0) = "SPL"

End If

If TRNS_ROW.Value = "SPL" And TRNS_ROW.Offset(1, 0) = "" Then

TRNS_ROW.Offset(1, 0) = "SPL"

End If

Set TRNS_ROW = TRNS_ROW.Offset(1)

Wend

End Sub

Sub InsertENDTRNS()

Dim export As Worksheet
Dim UnnecessaryRow As Range
Dim TRNS_ROW As Range

'Set UnnecessaryRow = Range("A3")

'UnnecessaryRow.EntireRow.Delete

Set TRNS_ROW = Range("A4")

While TRNS_ROW.Value <> ""

If TRNS_ROW.Value = "TRNS" And TRNS_ROW.Offset(-1, 0) <> "ENDTRNS" Then

TRNS_ROW.EntireRow.Insert

TRNS_ROW.Offset(-1, 0) = "ENDTRNS"

End If

If TRNS_ROW.Value = "SPL" And TRNS_ROW.Offset(1, 0) = "" Then

TRNS_ROW.Offset(1, 0) = "ENDTRNS"

End If

Set TRNS_ROW = TRNS_ROW.Offset(1)

Wend

End Sub