CODE

Common movements such as fixed assets, prepayments, accruals have their movement calculated and picked up through an index match on the journal schedule. These are then cleaned/re-formatted to what is required for an import to Xero. This can be dropped into Xero directly.

If you're interested in seeing the code below, please reach out @sidtrengove

Sub ExportUpload()

Application.StatusBar = "Please wait ... calculations in progress"
DoEvents
Application.ScreenUpdating = False

Dim rng As Range
Dim xWs As Workbook
Dim xcsvFile As String
xcsvFile = ActiveWorkbook.Path & "\Journals.csv"
Debug.Print (ActiveWorkbook.Path)
Debug.Print (xcsvFile)

Sheet26.Select
Range("I:L").Copy
Range("M:P").PasteSpecial xlPasteValues

Sheet26.Copy
Set Ws = ActiveWorkbook.Sheets("Journals")

lastRow = Range("B100").End(xlUp).Row
Range("G8").Formula = "=D8-E8"
Range("G8").Copy
Range("G8:G" & lastRow).PasteSpecial xlPasteAll
Range("G8:G" & lastRow).Copy
Range("G8:G" & lastRow).PasteSpecial xlPasteValues

Application.CutCopyMode = False

Columns("I:L").Delete

Set rng = Ws.Range("I1:I" & lastRow)

With rng
	.AutoFilter Field:=1, Criteria1:="<>1"
	.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

Ws.AutoFilterMode = False

Columns("H:H").Delete
Columns("A:E").Delete

Columns("C").Replace What:=1, Replacement:="NO VAT"

Range("A1") = "*AccountCode"
Range("B1") = "*Amount"
Range("C1") = "*TaxRate"
Range("D1") = "*Description"
Range("E1") = "*Date"
Range("F1") = "*Narration"


Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, _
FileFormat:=xlCSV, CreateBackup:=False
Application.ActiveWorkbook.Saved = True
		
'close the sheet
ActiveWorkbook.Save
Application.ActiveWorkbook.Close

Columns("M:P").ClearContents
Sheet5.Select

Application.ScreenUpdating = True
Application.StatusBar = ""

'open created
Workbooks.Open (xcsvFile)
Columns("A:D").AutoFit

MsgBox ("Please check this before uploading")
										
End Sub