VBA to copy and paste to new sheet, insert line based on above.

[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

 

[DanteAmor]

In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

You could put 3 examples (use XL2BB tool minisheet), as follows:
1) The data before running macros InsertOwnersDraw and InsertENDTRNS.
2) The data after running macros InsertOwnersDraw and InsertENDTRNS.
3) The data with the result you want with your request:
"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."

NOTE XL2BB:
It would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.