Excel VBA Macro to Add a Budget Value to the Correct Month and Category
Question details
The user needs to permanently record a budget amount under the correct month and category based on an entered date and selected category.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A budget workbook has an input amount, category selector, and date, and the user wants each transaction stored for later monthly and category-based reporting.
- Observed behavior
- Formulas can calculate the matching month and category but do not reliably preserve past transactions after the current date or input changes. Circular-reference formulas may retain values only with iterative calculation and can create maintenance problems.
Identify the cells or named ranges used for the transaction date, category, and amount, and confirm the budget summary contains month and category fields. If using VBA, save a macro-enabled copy of the workbook and enable the Developer tab.
Record each budget transaction with VBA and summarize it with SUMIFS
Use this method when every entered amount must remain as a permanent transaction record even after the selected month or category changes.
Store transactions in a dedicated Excel table instead of writing directly into a changing month grid. The summary sheet can then calculate totals from the stored date, category, and amount without circular references.
On a new worksheet, enter Date, Category, and Amount in cells A1:C1, select the range, choose Insert > Table, and name the table tblTransactions in Table Design > Table Name.
On the input sheet, place the entry cells in named ranges InputDate, InputCategory, and InputAmount. Use Data > Data Validation > List to create the category drop-down for InputCategory.
Open Developer > Visual Basic, choose Insert > Module, and paste this macro: Sub RecordBudgetTransaction()\n Dim ws As Worksheet\n Dim tbl As ListObject\n Dim newRow As ListRow\n Set ws = ThisWorkbook.Worksheets("Transactions")\n Set tbl = ws.ListObjects("tblTransactions")\n If Range("InputDate").Value = "" Or Range("InputCategory").Value = "" Or Range("InputAmount").Value = "" Then\n MsgBox "Enter a date, category, and amount first.", vbExclamation\n Exit Sub\n End If\n Set newRow = tbl.ListRows.Add\n newRow.Range(1, 1).Value = Range("InputDate").Value\n newRow.Range(1, 2).Value = Range("InputCategory").Value\n newRow.Range(1, 3).Value = Range("InputAmount").Value\n MsgBox "Transaction recorded.", vbInformation\nEnd Sub
Return to the worksheet, choose Developer > Insert > Button (Form Control), draw the button beside the input fields, select RecordBudgetTransaction, and click OK.
On the Budget sheet, use SUMIFS with the transaction table, such as =SUMIFS(tblTransactions[Amount],tblTransactions[Category],$A2,tblTransactions[Date],">="&B$1,tblTransactions[Date],"<"&EDATE(B$1,1)), where A2 contains the category and B1 contains the first day of the month.
Use a manual transaction table without VBA
Choose this option when macros are unavailable or the workbook should remain in a standard .xlsx format.
Avoid using a circular-reference formula to retain budget values
Use this guidance when a self-referencing formula appears attractive but the workbook needs predictable calculations and easy maintenance.
Record and summarize budget transactions in WPS Spreadsheet
WPS Spreadsheet can use a transaction table, category drop-down, VBA-supported macro workflow, and SUMIFS formulas to preserve budget entries and report them by month and category. Use a WPS version with VBA support when automating the Record button; otherwise, enter transactions directly in the table.
- 1. Create the transaction table: In WPS Spreadsheet, choose Insert > Table and create Date, Category, and Amount columns on a Transactions sheet.
- 2. Add the input controls: Use Data > Validity to create a category list, then prepare cells for the transaction date, category, and amount.
- 3. Automate or enter the record: On a VBA-supported WPS installation, open Tools > Macro > Visual Basic Editor and add a macro that appends the three input values to the transaction table. If VBA is unavailable, enter each transaction directly in the table.
- 4. Build the monthly summary: On the Budget sheet, use SUMIFS with the transaction table and month boundary dates to total each category for the selected month.
Frequently Asked Questions
Why does a formula not permanently add a budget amount to the previous month?
A formula recalculates from its current inputs; it does not create a new historical record when the date or category changes. Store each transaction in a table or append it with a macro instead.
Can a circular reference retain the previous budget value?
It can when iterative calculation is enabled and the formula is designed for that purpose, but circular references can produce unexpected results and make troubleshooting difficult. A transaction table with SUMIFS is generally safer.
How do I make sure the amount goes to the correct month and category?
Record the actual transaction date and category in separate table columns, then use SUMIFS with the category criterion and a date range from the first day of the target month to the first day of the next month.
Can I use the VBA macro if my workbook is saved as .xlsx?
A workbook containing VBA must be saved as .xlsm or another macro-enabled format. If macros are not required, use the manual transaction-table method and save the file as .xlsx.
