Excel VBA export from Pivot table

[shophoney]

Hi,

I have a pivot table based on data and need to run VBA to format for export to QuickBooks.

Below is the table and I need the data from the table on one line with the "j" column for the TRNS line, excluding "i" column.

And the same info but with not the "j" but "i" column leaving out "j".

This is the end result I want:

I want to copy and shift the data to a new tab to be exported for QuickBooks.



This is the CODE i have to start with.

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




Hope this makes sense, thanks