Paste Data into an Excel Row from a User-Selected Row Number
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.

- 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.
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.
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.
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.
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
Return to Sheet1 and type a row number in cell A1. For example, enter 10 to paste the source range into cells A10:C10.
Select Developer > Macros, choose PasteDataBasedOnInput, and click Run. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) to keep the code.

Assign the Row-Paste Macro to a Worksheet Button
Use a button when users need a simple worksheet control instead of opening the Macros dialog.
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. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the input cell and source data.
- 2. Set the target row: Select the configured input cell, such as A1, and enter or choose the destination row number.
- 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. Run the available macro: Use the workbook's macro command or assigned worksheet button to copy the source range to the requested destination row.

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.




