WPS Spreadsheets VBA: Move Rows When Text Is Found
Question details
The user needs to automate moving complete rows from a source worksheet to a destination worksheet when a specified text string appears.
- Product
- WPS Spreadsheets VBA
- Device & OS
- not provided
- Scenario
- A workbook contains source data that must be filtered by text and transferred to another worksheet automatically.
- Observed behavior
- Matching rows need to be identified, copied to the next empty destination row, and removed from the source worksheet without skipping adjacent matches.
Confirm the exact source and destination worksheet names, the column to search, and the text string to find. Save the workbook as a macro-enabled .xlsm file before running VBA code.
Use VBA to move matching rows safely between worksheets
Use this solution when you need to find text within a specific column, move each matching row, and remove it from the source sheet.
The macro searches from the last row upward, which prevents row deletion from causing the next matching row to be skipped. InStr with vbTextCompare finds the text without requiring an exact case match.
Open the workbook in WPS Spreadsheets and press Alt+F11. In the VBA editor, select Insert > Module to create a standard module.
Paste this macro into the module, then replace SourceSheet, DestinationSheet, SpecificText, and column A with your actual worksheet names, search text, and search column: Sub MoveDataBasedOnText() Dim wsSource As Worksheet Dim wsDestination As Worksheet Dim searchText As String Dim searchColumn As Long Dim lastRow As Long Dim destinationRow As Long Dim rowNumber As Long Set wsSource = ThisWorkbook.Worksheets("SourceSheet") Set wsDestination = ThisWorkbook.Worksheets("DestinationSheet") searchText = "SpecificText" searchColumn = 1 lastRow = wsSource.Cells(wsSource.Rows.Count, searchColumn).End(xlUp).Row For rowNumber = lastRow To 1 Step -1 If InStr(1, CStr(wsSource.Cells(rowNumber, searchColumn).Value), searchText, vbTextCompare) > 0 Then destinationRow = wsDestination.Cells(wsDestination.Rows.Count, 1).End(xlUp).Row + 1 wsSource.Rows(rowNumber).Copy Destination:=wsDestination.Rows(destinationRow) wsSource.Rows(rowNumber).Delete End If Next rowNumber End Sub
Return to WPS Spreadsheets, press Alt+F8, select MoveDataBasedOnText, and click Run. The macro copies each matching source row to the next available row on the destination worksheet before deleting it from the source.
Choose File > Save As, select the Excel Macro-Enabled Workbook (*.xlsm) format, and confirm the format choice so the VBA code remains available.
Automate text-based row transfers with WPS Spreadsheets
WPS Spreadsheets lets you work with macro-enabled workbooks and use VBA automation to find text, transfer complete rows between worksheets, and preserve the original workbook structure.
- 1. Open your workbook: Launch WPS Office, open the source workbook in WPS Spreadsheets, and verify the source and destination worksheet tabs.
- 2. Open the VBA editor: Press Alt+F11, choose Insert > Module, and add the row-transfer macro.
- 3. Set the matching rules: Edit the worksheet names, searchText value, and searchColumn number in the macro.
- 4. Run and save: Press Alt+F8 to run the macro, then use File > Save As and select the XLSM format.
Frequently Asked Questions
How do I change which column the macro searches?
In the macro, change searchColumn = 1 to the column number you need. Use 1 for column A, 2 for column B, 3 for column C, and so on.
Does the macro require an exact text match?
No. InStr searches for the specified text anywhere in the cell value. The vbTextCompare option makes the search case-insensitive.
Why does the macro loop from the bottom row upward?
Deleting a row shifts all rows below it upward. A bottom-to-top loop prevents the next row from being skipped after a match is deleted.
Can I keep matching rows in the source worksheet?
Yes. Remove or comment out the line wsSource.Rows(rowNumber).Delete if you only want to copy matching rows without deleting them from the source.
What file format preserves the VBA macro?
Save the workbook as an Excel Macro-Enabled Workbook with the .xlsm extension. Standard .xlsx files do not preserve VBA code.
