VBA & Macro Problems

How to Use a VBA Macro to Find and Copy Monthly Transactions in Spreadsheet

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs a way to search a Transaction Overview sheet by month, year, and type, and then copy the matching income and expense rows to corresponding monthly sheets.

Product: Spreadsheet
Device & OS: not provided
Scenario: Organizing a master transaction log into monthly income and expense reports, and separately filtering for specific events.
Observed behavior: Creating a reliable VBA macro is difficult without knowing the exact worksheet structure, often requiring formulas, filters, or a sample workbook for an accurate solution.

Before you start

Before writing VBA code or applying complex formulas, clearly define your column headers for Month, Year, and Transaction Type to ensure data is referenced accurately.

Solution 1Recommended

Use Built-in Advanced Filters Instead of VBA

For complex or frequently changing data structures, built-in filter features are often more stable and easier to implement than custom macros.

Format as Table

Select your Transaction Overview data and press Ctrl+T to format it as a table. This makes filtering dynamic and more manageable.

Apply Filters

Click the 'Data' tab and select 'Filter'. Use the drop-down arrows on the Month, Year, and Transaction Type columns to display only specific income or expense rows.

Copy the Filtered Data

Highlight the visible filtered rows, press Ctrl+C to copy them, navigate to your target monthly worksheet, and press Ctrl+V to paste the values.

Solution 2

Prepare a Simplified Sample Workbook for VBA Creation

If a custom macro is strictly required, you must first define the exact structure of your data so the code can reference the correct ranges.

Solution 3

Write a Basic VBA Macro to Filter and Copy Rows

Use a standard VBA loop to check conditions such as transaction type or specific events and copy matching rows to another sheet.

Efficient Data Management

Easily Manage Macros and Financial Data with WPS Spreadsheet

WPS Spreadsheet fully supports VBA/Macros and provides powerful data analysis tools like Advanced Filters and Pivot Tables, making it effortless to categorize your monthly income and expenses automatically.

1. Enable Macros: Open your macro-enabled workbook in WPS Spreadsheet and click 'Enable Macros' in the security warning prompt at the top of the screen.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon to access the VBA Editor, view your Macros list, and insert form controls.
3. Analyze Data Without Code: Alternatively, use the 'Data' tab to create dynamic Pivot Tables that instantly group your transactions by month, year, and type without requiring any complex VBA.

Full compatibility with Microsoft Excel .xlsm and .xlsx formatsBuilt-in VBA editor for writing, editing, and running custom macrosAdvanced filtering and Pivot Table tools for codeless data summarizationLightweight installation with high processing speed for large transaction logs

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro not copying the correct transaction rows?

This typically happens when the column references in your VBA code do not match the actual layout of your data. Double-check your column letters/indices and ensure there are no hidden columns shifting your data.

Can I filter transaction data by multiple criteria using a macro?

Yes. You can use the 'AutoFilter' method in VBA to apply multiple consecutive filters across different columns (such as filtering by Month, Year, and Transaction Type simultaneously) before copying the visible range.

Are Pivot Tables better than VBA for generating monthly summaries?

In many cases, yes. Pivot Tables are native, highly dynamic, and less prone to breaking when your data structure changes slightly. VBA requires manual code updates if your column headers move.

How do I safely share a macro-enabled workbook to get help online?

Create a copy of the file, delete all sensitive financial data, replace it with generic text, save it as an .xlsm file, and upload it to a trusted cloud service. Provide the view-only link to the support forum.

Excel & Data Problems