IMPORT TB

WORKING PAPERS: EXCEL & VBA

Exporting and importing the TB is a relatively quick task however can become very repetitive when having to complete this step multiple times throughout the preparation phase.

Initial Monthly Process (5-20mins)

  1. Check TB after postings
  2. Match to each schedule
  3. Update as necessary cells
  4. Correct for errors
  5. Check TB post correction

New Monthly Process (4-5min)

  1. Export TB
  2. Save to current DIR
  3. Click button
  4. Fill down sum formula

CODE

Grab the TB from current DIR, take relevant data and paste into given range in current Workbook.

Sub ImportTB()

'Tell user to wait
Application.StatusBar = "Please wait ... calculations in progress"
DoEvents
Application.ScreenUpdating = False

'Remove existing data from TB
Sheets("TB").Range("B7:F100").ClearContents

'Create and assign variables
Dim Location As String

'Save locations
Location = Application.ActiveWorkbook.Path + "\TB.xlsx"

'Open a workbook
Workbooks.Open Location, UpdateLinks:=0

'Copy data from csv
Workbooks("TB.xlsx").Worksheets("Trial Balance").Range("A6:E100").Copy

'Paste data in management accounts
Workbooks("Working Papers.xlsm").Worksheets("TB").Range("B7").PasteSpecial xlPasteAll

'End copy paste mode
Application.CutCopyMode = False

'Close a workbook
Workbooks("TB.xlsx").Close SaveChanges:=False

'Get back to Action Tab
Workbooks("Working Papers.xlsm").Worksheets("TB").Range("A1").Select
Sheet5.Select

'Turn sheet back to normal
Application.ScreenUpdating = True
Application.StatusBar = ""
										
End Sub