VBA & Macro Problems

How to Copy Rows to Another Sheet in Excel VBA Without Selecting Them

Ayan MasoodAyan Masood Nov 13, 2026 869 views

Question details

The user needs to use Excel VBA to copy the active row or selected data to a final worksheet without manually selecting the row first, and optionally automate this with a button while skipping blank rows.

Product
WPS Office Spreadsheets (VBA/Macro)
Device & OS
not provided
Scenario
The user is working in a workbook and wants to move the current row or selected rows to a final worksheet using a macro instead of manual copy and paste.
Observed behavior
The goal is to have a VBA macro that copies the active row to the next available row on the final sheet, can be assigned to a button, and skips blank rows.
Before you start

Open the workbook in WPS Office and confirm that the destination sheet exists or update the macro sheet name to match. Enable macros and make sure the VBA editor is available so the macro can be pasted and run.

Solution 1Recommended

Copy the Active Row Without Selecting It Using a VBA Macro

Use this solution when you want to copy the row containing the active cell to the next blank row on a final worksheet without selecting the source or destination.

The macro uses ActiveCell.Row and UsedRange.Columns.Count to build a source range for the current row, then writes to the next empty row on the destination sheet. This avoids selecting the source or destination range.

1
Open the VBA editor

Press Alt+F11 in WPS Office Spreadsheets to open the Microsoft Visual Basic for Applications editor.

2
Insert a new module

In the Project Explorer, right-click the workbook VBAProject, choose Insert > Module, and open the new module.

3
Paste the copy-row macro

Paste the following macro into the module: Sub CopyRow() Dim sourceRow As Range Dim targetRow As Range Set sourceRow = ActiveSheet.Cells(ActiveCell.Row, 1).Resize(1, ActiveSheet.UsedRange.Columns.Count) Set targetRow = Worksheets("Final Sheet").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) sourceRow.Copy Destination:=targetRow End Sub

Copy the Active Row Without Selecting It Using a VBA Macro
Macro ready to run: The macro identifies the active row as a source range and copies it to the next blank row on the sheet named “Final Sheet” without selecting the row first.
WPS Office Spreadsheets VBA

Copy Rows to Another Sheet with Excel VBA in WPS Office Spreadsheets

Use the VBA editor in WPS Office Spreadsheets to run a macro that copies the active row to a final worksheet without selecting it. The same VBA-compatible environment lets you add a button and skip blank rows, so the copy process is fast and repeatable.

  1. 1. Download WPS Office: Visit the official WPS Office download page and download WPS Office Free for your Windows or macOS device.
  2. 2. Install and open your workbook: Install WPS Office, open the workbook containing the source rows, and make sure the destination sheet exists.
  3. 3. Open the VBA editor: Press Alt+F11 to open the VBA editor, insert a module, and paste the CopyRow macro.
  4. 4. Run or assign the macro: Run the macro from the VBA editor, or use the Developer tab to insert a button and assign the macro to it.
  5. 5. Test the copy: Click the active row and run the macro; verify that the row appears in the next blank row of the final sheet.
Runs familiar Excel VBA syntax for copying rows without selecting themSupports .xls, .xlsx, and .xlsm workbook formatsLets you assign the macro to a button for one-click copyingFree and lightweight Office suite that can open existing workbooks
microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro copy the active row instead of the selected range?

The sample macro uses ActiveCell.Row and Resize to span the used columns, so it targets the row containing the active cell. To copy multiple selected rows, use a loop over Selection.Rows instead.

How do I skip blank rows when copying multiple rows?

Add a check such as If Application.WorksheetFunction.CountA(sourceRow) > 0 Then sourceRow.Copy Destination:=targetRow, and only advance the target row when a row contains data.

How do I assign the macro to a button in WPS Office?

Display the Developer tab, click Insert > Button (Form Control), draw the button on the sheet, and select the macro from the Assign Macro dialog.

The destination sheet name is different. What should I change?

Replace Worksheets("Final Sheet") with your actual sheet name, keeping the quotes, for example Worksheets("Archive").

Does WPS Office support Excel VBA macros?

WPS Office Spreadsheets supports VBA-compatible macros, so you can open Excel macro-enabled workbooks and use familiar VBA code; some advanced features may differ.