How to Export Rows Based on Criteria using VBA Macro in Excel and WPS
Question details
The user needs a VBA macro to selectively copy specific columns of data to a new workbook based on a numerical condition in one column, while managing formatting and formulas.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Filtering data rows where column AF equals 1, copying columns A through Y to a new workbook, and converting the copied formulas to plain text except for a specific designated cell.
- Observed behavior
- The macro needs to identify rows matching the criteria, safely export the defined range, retain source formatting, and execute selective formula-to-value conversion without breaking the logic.
Ensure you have a backup of your original workbook and that Developer tools and VBA are enabled in your spreadsheet application.
Use a VBA Script to Loop, Filter, and Export Rows
Write a VBA macro that loops through the target column to identify matching criteria, copies the required range to a new workbook, and uses PasteSpecial to handle formatting and values.
To achieve this, the macro needs to read the dataset dynamically, isolate rows where column AF equals 1, and process columns A:Y. Using the PasteSpecial method allows you to separate the pasting of values from the pasting of formats, fulfilling the requirement to convert formulas to text while keeping the layout intact.
For the specific cell exception (the yellow cell), an IF condition based on the cell address or interior color index must be added inside the formula-conversion loop.
Launch your Spreadsheet application, open your workbook, and press Alt + F11 to open the Visual Basic Editor (VBE). Go to Insert > Module to create a new script module.
In the new module, declare your variables for the source worksheet and the destination workbook. Create a For loop that iterates from the first row of your data down to the last used row in column AF.
Inside the loop, write an If statement: `If Cells(i, "AF").Value = 1 Then`. If true, define the range to copy using `Range(Cells(i, "A"), Cells(i, "Y")).Copy`.
Activate the new workbook and use `Selection.PasteSpecial Paste:=xlPasteValues` to convert formulas to text. Follow this immediately with `Selection.PasteSpecial Paste:=xlPasteFormats` to preserve the source formatting.
To skip converting the 'yellow cell' formula, you can either retain the original formula by copying that specific cell separately, or use a VBA check `If Not cell.Address = "$A$1" Then` (replacing $A$1 with your cell) before converting formulas to values.
Use WPS Spreadsheet to Run VBA Macros Seamlessly
WPS Spreadsheet fully supports VBA macros, allowing you to easily automate complex data filtering, export conditional rows, and format data across multiple workbooks.
- 1. Open Your Macro File: Launch WPS Spreadsheet and open your .xlsm or .xls file.
- 2. Access the Developer Tab: Click on the 'Developer' tab on the top ribbon.
- 3. Open the VBA Editor: Click the 'Visual Basic' button or press Alt + F11 to launch the integrated VBA Editor.
- 4. Run Your Export Macro: Paste your custom code into a module and click 'Run' (or press F5) to execute the row-export sequence.
Frequently Asked Questions
Why is my VBA macro not copying the original cell colors and borders?
Standard copy-paste operations might only bring over values or formulas depending on how they are scripted. To preserve cell colors, borders, and fonts, you must use `.PasteSpecial Paste:=xlPasteFormats` in your code after pasting the values.
How do I exclude a specific cell from being converted from a formula to text?
Instead of converting an entire copied range to values at once, you can loop through the cells in the destination range. Use an `If` statement to check if the cell's address (or background color index) matches your exception. If it does not match, apply `.Value = .Value`.
Does WPS Office support VBA for Excel macros?
Yes, WPS Office supports VBA macros natively in its professional and business editions, providing high compatibility with Microsoft Excel's VBA objects, properties, and methods.
