WPS Spreadsheets VBA: Search External Workbook and Copy Matching Rows
Question details
The user needs to automate a VBA search in WPS Spreadsheets that opens an external workbook, qualifies all references to that workbook, finds rows matching criteria in columns 1 through 4, copies columns 1 through 18 for each matching row, and pastes them into a calculated destination row.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is writing a VBA macro to extract matching rows from an external workbook into a destination worksheet.
- Observed behavior
- Without fully qualified references, the macro can search or write to the wrong workbook; opening the external workbook once and qualifying source and destination worksheet references prevents this.
Confirm the external workbook path and worksheet name before running the macro, and make sure the destination workbook is open in WPS Spreadsheets. If you are storing the VBA code in a file, save it as a macro-enabled workbook format so macros can run.
Use Fully Qualified VBA References to Search an External Workbook and Copy Matching Rows
Use this when the macro must reliably open an external workbook once, test each source row against criteria in columns 1 through 4, copy columns 1 through 18 for matches, and paste into a destination row.
The key is to qualify every worksheet and range with the external workbook object, such as Wb2.Worksheets("Sheet1") and sourceWs.Range(sourceWs.Cells(i, 1), sourceWs.Cells(i, 18)). This prevents WPS Spreadsheets from accidentally searching or writing to the wrong workbook.
Declare row counters as Long rather than Integer, and close Wb2 only after the loop finishes.
In WPS Spreadsheets, press Alt+F11 to open the VBA editor, then click Insert > Module to create a new module.
At the top of the procedure, use Long for row counters and set the destination worksheet explicitly, for example: Dim Wb2 As Workbook, sourceWs As Worksheet, destWs As Worksheet, i As Long, destRow As Long.
Before the loop, run Workbooks.Open to open the external workbook and assign the source sheet: Set Wb2 = Workbooks.Open("C:\YourFile.xlsx") and Set sourceWs = Wb2.Worksheets("Sheet1").
Inside the loop, test sourceWs.Cells(i, 1) through sourceWs.Cells(i, 4) for your criteria. When matched, copy sourceWs.Range(sourceWs.Cells(i, 1), sourceWs.Cells(i, 18)) and paste it into the destination worksheet at a calculated destination row.
After the loop finishes, close Wb2 once with Wb2.Close SaveChanges:=False. Do not close it inside each loop iteration.
Use the FILTER Function to Pull Matching Rows Without VBA
Use this alternative when the criteria are simple and you can keep the external workbook open while WPS Spreadsheets recalculates the FILTER formula.
Run Qualified External Workbook VBA Searches in WPS Spreadsheets
WPS Spreadsheets lets you run the same VBA workflow: open an external workbook once, fully qualify source and destination worksheet references, loop through rows, copy matching columns, and paste into the destination sheet.
- 1. Open WPS Spreadsheets and enable the VBA editor: Launch WPS Spreadsheets, open the destination workbook, and press Alt+F11 to access the VBA editor.
- 2. Insert a module and paste the qualified macro: Click Insert > Module, then paste a macro that uses Workbooks.Open, fully qualified sourceWs and destWs references, Long row counters, and copies matching rows.
- 3. Run the macro: Close the VBA editor, return to WPS Spreadsheets, click Developer > Macros, select the macro, and click Run.
- 4. Verify the copied matching rows: Check the destination worksheet for the rows copied from columns 1 through 18 and confirm the external workbook closes without saving.
Frequently Asked Questions
Why does my VBA macro search or copy from the wrong workbook?
The most common cause is unqualified references. Use the external workbook object and source worksheet, for example Wb2.Worksheets("Sheet1").Cells(i, 1), and also qualify the destination worksheet explicitly before copying.
Should I open and close the external workbook for each row in the loop?
No. Open Wb2 once before the loop, test and copy the matching rows, and close it once after the loop with Wb2.Close SaveChanges:=False. Closing it during each iteration is slow and can cause the loop to fail.
Why use Long instead of Integer for VBA row counters?
Long is safer for row counts because WPS Spreadsheets and Excel worksheets can exceed the 32,767 limit of Integer. Declare row counters as Long to avoid overflow errors when looping through large sheets.
Can I get the same matching rows without using VBA in WPS Spreadsheets?
Yes. Open both workbooks and use the FILTER function with an external workbook reference, such as =FILTER([External.xlsx]Sheet1!A1:R100,([External.xlsx]Sheet1!A1:A100=D1)*([External.xlsx]Sheet1!B1:B100=E1)). The external workbook must remain open while the formula recalculates.
