How to Copy Only Selected Columns from Matching Rows with VBA in WPS Spreadsheets
Question details
The user needs a VBA macro in WPS Spreadsheets that finds rows where column E contains CHECK and copies only columns A, C, and D to another worksheet.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A macro scans a source sheet for matching rows and writes selected column values to a destination sheet.
- Observed behavior
- The expected result is three destination columns per matching row, excluding column B and other source columns.
Open the workbook in WPS Spreadsheets and confirm that the source sheet is named PBS and the destination sheet is named CHECK, with column E containing the exact value CHECK.
Use a Find-loop VBA macro to write only columns A, C, and D to the target sheet
Use this method when you need an automated, repeatable macro that skips column B and copies only the selected columns from matching rows.
The macro uses Range.Find on column E to locate each cell containing CHECK. When a match is found, it writes a three-column array from the matching row: offset -4 from column E is column A, offset -2 is column C, and offset -1 is column D.
In WPS Spreadsheets, press Alt+F11, or click Developer > Visual Basic to open the VBA editor.
In the editor, click Insert > Module to create a new standard module.
Paste the following code into the module: Sub CopyCells() Dim PBS As Worksheet, CHECK As Worksheet, Cell As Range, Target As Range, FirstAddress As String Set PBS = Worksheets("PBS") Set CHECK = Worksheets("CHECK") Set Target = CHECK.Cells(CHECK.Rows.Count, "A").End(xlUp) Set Cell = PBS.Columns("E").Find(What:="CHECK", LookAt:=xlWhole) If Not Cell Is Nothing Then FirstAddress = Cell.Address Do Set Target = Target.Offset(1) Target.Resize(1, 3).Value = Array(Cell.Offset(0, -4).Value, Cell.Offset(0, -2).Value, Cell.Offset(0, -1).Value) Set Cell = PBS.Columns("E").Find(What:="CHECK", After:=Cell, LookAt:=xlWhole) Loop While Not Cell Is Nothing And Cell.Address <> FirstAddress End If End Sub
Click Run > Run Sub/UserForm or press F5, then switch back to the CHECK sheet and confirm that columns A:C contain the copied values.
Filter and copy selected columns manually
Use this for a quick, one-time copy when you do not need a reusable VBA macro.
Use WPS Spreadsheets VBA to Copy Selected Columns from Matching Rows
Write and run a VBA macro in WPS Spreadsheets to find CHECK in column E and copy only columns A and C:D into another worksheet.
- 1. Open the workbook: Launch WPS Office, click WPS Spreadsheets, and open the workbook that contains the PBS and CHECK sheets.
- 2. Open the VBA editor: Press Alt+F11, or click Developer > Visual Basic to open the VBA editor.
- 3. Insert and paste the macro: Click Insert > Module, paste the macro, and adjust PBS and CHECK to your actual sheet names.
- 4. Run the macro: Click Run > Run Sub/UserForm or press F5 to execute the macro.
- 5. Check the destination sheet: Switch to the CHECK sheet and confirm that columns A:C now contain source columns A, C, and D for every matching row.
Frequently Asked Questions
Can I use this VBA macro in WPS Spreadsheets if my workbook was created in Microsoft Excel?
Yes. WPS Spreadsheets supports VBA macros and can open .xlsm workbooks. Open the file, press Alt+F11, insert a module, and run the macro.
How do I change the source and destination sheet names in the macro?
Edit the lines Set PBS = Worksheets("PBS") and Set CHECK = Worksheets("CHECK") to use your actual sheet names, keeping the double quotation marks.
Why does the macro place three values in a row instead of copying cells individually?
The Target.Resize(1, 3).Value = Array(...) statement writes a three-column array in one operation. The offsets -4, -2, and -1 refer to columns A, C, and D relative to the matching cell in column E.
What should I check if no rows are copied even though column E contains CHECK?
Confirm that column E cells contain exactly CHECK with no extra spaces, and that LookAt:=xlWhole is used. If the value case differs, change the search text to match your data.
Can I modify the macro to copy additional columns?
Yes. Add the corresponding Offset values to the Array and change Resize(1, 3) to match the number of columns you want to write.
