VBA & Macro Problems

How to Export Transactions by Account Number Using VBA Macro in Excel

Maira Mehtab Oct 1, 2026 868 views

Question details

The user needs an Excel VBA macro to filter transactions based on account numbers and export the matched records to a different worksheet or workbook.

How to Export Transactions by Account Number Using an Excel VBA Macro

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating the extraction of specific financial or transactional records based on unique account codes.
Observed behavior: Requires a customized VBA script to loop through data, apply filters by account number, and copy the results to a specified destination.

Before you start

Before writing your macro, ensure you have clearly identified the column containing the account numbers, the destination for your exported records, and remember to save your file as an Excel Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Define Parameters and Create the Export Macro

Use a basic VBA script structure to filter your dataset by a specific account number and copy the visible rows to a new location.

Since every dataset is different, the exact VBA code depends on your specific column layouts. The core logic involves applying an AutoFilter to the account number column, copying the visible cells, and pasting them into the target worksheet or workbook.

Open the VBA Editor

Press ALT + F11 in Excel to open the Visual Basic for Applications editor.

Insert a new Module

Click Insert > Module from the top menu to create a blank script window where you will write the code.

Define the Macro Variables

Write your Sub routine and declare your variables for the source worksheet, destination worksheet, and the target account number.

Apply the AutoFilter

Write code using Range.AutoFilter targeting the specific column with your account numbers, using the desired account code as the criteria.

Copy and Paste the Results

Use the SpecialCells(xlCellTypeVisible).Copy method to copy only the filtered transactions, then use the PasteSpecial method on the destination sheet's starting cell.

Define Parameters and Create the Export Macro

Customize Your Code: You will need to replace generic range references (like 'A1:D100') and column indices in the code with the actual layout of your transactional data.

Automate Tasks with WPS Spreadsheet

Use WPS Office to Create and Run Export Macros Seamlessly

WPS Spreadsheet offers robust VBA support, allowing you to create, edit, and execute complex macros just like in Microsoft Excel. Automate your transaction exports effortlessly in a familiar, high-performance environment.

1. Install WPS Office: Download and install WPS Office, then open your transactional spreadsheet.
2. Access Macro Tools: Navigate to the Developer tab on the top ribbon.
3. Open VBA Editor: Click the VBA Editor button to launch the coding environment.
4. Insert Script: Insert a new module and paste your account filtering VBA script.
5. Run Macro: Click the Run button or press F5 to instantly filter and export your transactions.

Fully compatible with Microsoft Excel (.xls, .xlsx, and .xlsm) file formats.Built-in VBA editor to write and execute transaction filtering macros.Free, lightweight, and fast-loading spreadsheet application.Familiar user interface requiring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro copying hidden rows instead of just the filtered transactions?

If hidden rows are being copied, ensure your macro uses the SpecialCells(xlCellTypeVisible) property before the .Copy command. This forces the application to ignore rows hidden by the AutoFilter.

Can I export transactions for multiple account numbers at once?

Yes, you can modify the macro to loop through a list of account numbers. By using a 'For Each' loop, the macro can apply the filter for each account code sequentially and append the results to the destination sheet.

How do I export the filtered transactions to a completely new workbook?

Instead of referencing a sheet within the current workbook, you can add 'Workbooks.Add' to your VBA code to generate a new file, paste the copied transaction data into it, and optionally save it automatically using 'ActiveWorkbook.SaveAs'.

Will my VBA code work in WPS Office?

Yes, WPS Spreadsheet supports VBA. As long as you have the VBA module enabled in your WPS Office version, standard macros for filtering and copying data will run without compatibility issues.

Excel & Data Problems