VBA & Macro Problems

Paste Data into an Excel Row from a User-Selected Row Number

Steve KSteve K Nov 12, 2026 870 views

Question details

Create a VBA macro that reads a target row number from an input cell or dropdown and copies a defined data range to that row.

Paste Data into an Excel Row Based on User Input
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
The user wants to choose a worksheet row number, then paste data into that row automatically.
Observed behavior
The macro should validate the entered row number and copy the selected source range beginning in the destination row's first column.
Before you start

Enter the destination row number in the input cell, such as A1, and confirm that the source range does not overwrite the input cell. Enable macros when opening the workbook if Excel displays a security warning.

Solution 1Recommended

Use VBA to Copy a Source Range to the Row Entered in an Input Cell

Use this method when the target row is entered manually or selected through a dropdown in a worksheet cell.

This example reads the destination row from Sheet1!A1 and copies the values and formatting from A2:C2 to columns A:C on the selected row. Change the sheet name, input cell, source range, or destination column to match your workbook.

1
Open the Visual Basic editor

In Excel, open the Developer tab and select Visual Basic. If the Developer tab is hidden, go to File > Options > Customize Ribbon and select Developer.

2
Insert a standard module

In the Visual Basic editor, select Insert > Module, then paste the following code into the new module: Sub PasteDataBasedOnInput() Dim ws As Worksheet Dim dataToPaste As Range Dim targetRow As Long Set ws = ThisWorkbook.Worksheets("Sheet1") If Not IsNumeric(ws.Range("A1").Value) Then MsgBox "Enter a valid target row number in cell A1." Exit Sub End If targetRow = CLng(ws.Range("A1").Value) Set dataToPaste = ws.Range("A2:C2") If targetRow >= 1 And targetRow <= ws.Rows.Count Then dataToPaste.Copy Destination:=ws.Cells(targetRow, 1) Else MsgBox "Invalid target row number." End If End Sub

3
Enter the destination row number

Return to Sheet1 and type a row number in cell A1. For example, enter 10 to paste the source range into cells A10:C10.

4
Run the macro

Select Developer > Macros, choose PasteDataBasedOnInput, and click Run. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) to keep the code.

Use VBA to Copy a Source Range to the Row Entered in an Input Cell
Adjust the destination position: The expression ws.Cells(targetRow, 1) starts the paste in column A. Change 1 to another column number, such as 4 for column D, when needed.
Work with spreadsheet macros

Paste Data into a Selected Row with WPS Spreadsheets

WPS Spreadsheets provides a familiar spreadsheet workspace for entering a target row number, managing source data, and running compatible VBA-style macros in supported desktop environments. You can open the workbook, edit the input cell, and use the macro workflow to place data in the required row.

  1. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the input cell and source data.
  2. 2. Set the target row: Select the configured input cell, such as A1, and enter or choose the destination row number.
  3. 3. Review the source range: Confirm that the data range, such as A2:C2, contains the values you want to place in the selected row.
  4. 4. Run the available macro: Use the workbook's macro command or assigned worksheet button to copy the source range to the requested destination row.
Enter or select the target row directly in a worksheet cell before running the paste macro.Use a familiar spreadsheet interface for ranges, cells, buttons, and formula-driven dropdown lists.Supports opening and editing Microsoft Excel .xlsx and macro-enabled .xlsm workbook formats.Lightweight WPS Office tools help you continue working with existing spreadsheet files.
microsoft office alternative - wps office

Frequently Asked Questions

How can I select the target row from a dropdown instead of typing it?

Create a dropdown in the input cell with Data > Data Validation > List, then use that same cell, such as A1, in the VBA line targetRow = CLng(ws.Range("A1").Value).

How do I paste into a column other than column A?

Change ws.Cells(targetRow, 1) in the macro. For example, use ws.Cells(targetRow, 4) to begin pasting in column D.

Will the macro copy formulas and formatting as well as values?

Yes. Range.Copy Destination copies cell contents, formulas, and formatting. To paste values only, replace the Copy line with ws.Cells(targetRow, 1).Resize(dataToPaste.Rows.Count, dataToPaste.Columns.Count).Value = dataToPaste.Value.

Why does Excel show an invalid target row message?

The value in the input cell is not a number or is outside the worksheet's available row range. Enter a whole row number of 1 or greater in the configured input cell.

Can I save this macro in a normal .xlsx workbook?

No. Save the workbook through File > Save As using the Excel Macro-Enabled Workbook (*.xlsm) format so that the VBA code is retained.