Was preparing my personal budget Excel workbook for next year. It contains a Summary worksheet and a worksheet for each month of the year. Copied this year’s workbook, renamed it and thought there must be a better way to clear the month worksheets and set recurring expenses. So I wrote 3 Excel VBA procedures (1 Function and 2 Subs). The Sub procedures can be run (after they have been saved) by going to the Developer tab, Macros and choosing the macro to run.
First off, a function to return the list of month names. This was covered in my previous post Return String Array in Excel VBA Macro.
Function GetMonths() Rem Difference btw Function and Sub is that the former can return values while the latter can't GetMonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec") End Function
Next, a macro to clear all content in each month worksheet. The first row in each worksheet is used for the header, hence the range starts from A2. There are 5 columns, A to E, the last being a remarks column. 1000 is just an arbitrary number – I don’t usually have so many income/expense records in a month.
Sub ClearMonths() Rem Clear all month worksheets Dim Month, Months Months = GetMonths() For i = LBound(Months) To UBound(Months) Month = Months(i) Sheets(Month).Select Range("A2:E1000").ClearContents Range("A2").Select Next i End Sub
Finally, a macro to set recurring expenses. Note that the amounts have been changed to arbitrary numbers 😛 For the Savings expense, it will be entered into every month (cells A4 to D4). For the Property Tax Instalment, it will only be entered from Jan to Sep.
Sub SetMonthlyExpenses() Rem Set recurring monthly expenses Dim Month, Months, StartRow, Expenses(1), Expense 'Expenses start from row 4 in blank worksheets 'Row 1 is header, row 2 left blank for income and row 3 as buffer Months = GetMonths() StartRow = 4 'Each row = Array(No. of months to enter expense starting from Jan, Day of month, Item, Category, Amount) Expenses(0) = Array(12, 1, "POSB MySavings", "Savings", 10) Expenses(1) = Array(9, 5, "Property Tax Instalment", "Property Tax", 1.23) For MonthIndex = LBound(Months) To UBound(Months) Month = Months(MonthIndex) Sheets(Month).Select ExpenseRow = StartRow For ExpenseIndex = LBound(Expenses) To UBound(Expenses) Expense = Expenses(ExpenseIndex) 'Break if no need to repeat expense from this month onwards If (MonthIndex + 1) <= Expense(0) Then 'Skip index 0 and special calculation for index 1 Range("A" & ExpenseRow) = Expense(1) & " " & Month For i = 2 To UBound(Expense) Range(Chr(65 + i - 1) & ExpenseRow).Value = Expense(i) Next i End If 'Increment row number ExpenseRow = ExpenseRow + 1 Next ExpenseIndex Next MonthIndex End Sub
Definitely beats manually clicking on 12 worksheets, selecting the range, clearing them, pasting the recurring expenses and modifying the dates! If you have any more questions, feel free to hit the Contact page - Adhuc!