Copy and Paste Values to Next Empty Row with Excel VBA (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

You can copy and paste data in several ways in Excel. Using the VBA code to copy and paste values with conditions can automate a lot of steps and save your time. In this article, you will learn to copy and paste values to the next empty row using Excel VBA code with 3 examples.


Copy and Paste Values to Next Empty Row Using Excel VBA: 3 Examples

1. Copy a Range of Cells and Paste Values from the Next Empty Row in Excel Using VBA Code

In the screenshot below, you can see a dataset. We are going to use a VBA code to copy any row from the following dataset and paste it to the next first blank row after the data table ends.

In this particular example, we will copy row 11 and paste it to the next blank row which is 12 using the VBA code.

To do that,

❶ Press ALT + F11 to open the VBA code editor.

❷ Then create a new Module from the Insert tab.

Create a new module to Copy a Range of Cells and Paste Values from the Next Empty Row in Excel Using VBA Code

❸ Then copy the following VBA code.

Sub PasteToNextEmptyRow()
Range("B11:E11").Copy
Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

End Sub

❹ Paste the above code in the VBA editor and save it.

Copy a Range of Cells and Paste Values from the Next Empty Row in Excel Using VBA Code

❺ Now hit the Run Sub button or press F5 to run the above code.

After that, you will see that the contents of row 11 have been copied to the next empty row which is 12.

Read More: Excel VBA to Copy Only Values to Destination


2. Copy the Contents of a Row and Paste Values to the Next Empty Row in Excel VBA with Condition

In this example, we will use a VBA code to copy and paste values to the next empty row. We will set up a condition such as when the row number is not blank up to a certain number of rows, it will display a message such as Limit Reached.

❶ First, open the VBA code editor by pressing ALT + F11.

❷ Then go to Insert > Module.

❸ After that copy the following VBA code.

Sub PasteValuesToNextEmptyRow()

Dim m As Long
m = WorksheetFunction.Max(3, Cells(Rows.Count, "B").End(xlUp).Row)
If m >= 12 Then
MsgBox "Limit Reached."
Exit Sub
End If
Range("B11:E11").Copy
Range("B" & m + 1).PasteSpecial xlPasteValues
End Sub

❹ Paste it and save it in the VBA editor.

Copy the Contents of a Row and Paste Values to the Next Empty Row in Excel VBA with Condition

❺ Now press F5 to run the above code.

This code will copy the contents of row 11 and paste it to the next empty row which is row 12.

We set up a condition in the code. When you reach the row limit up to row 12, it will show an alert which is Limit Reached. You can change the limit as per your requirement.

Read More: Excel VBA: Copy Row If Cell Value Matches


3. Copy the Contents of a Row and Paste Values to the Next Empty Row in Another Worksheet Using Excel VBA

Using this method you can copy data and paste values to the next empty row in another worksheet in Excel.

To do that,

❶ Press ALT + F11 to open the VBA editor.

❷ Click Insert > Module.

❸ Copy the following VBA code.

Private Sub PasteNextEmptyRowAnotherSheet()

Dim mm As Boolean
Dim nn As Worksheet
Dim xx As Range
Dim yy As String

On Error Resume Next
yy = ActiveWindow.RangeSelection.Address
Set xx = Application.InputBox("Insert a range:", "Microsoft Excel", yy, , , , , 8)
If xx Is Nothing Then Exit Sub
Set nn = Worksheets("Output")
mm = Application.ScreenUpdating
Application.ScreenUpdating = False
xx.Copy
nn.Cells(Rows.Count, 2).End(xlUp).Offset(11, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = mm

End Sub

❹ Paste and save the code in the VBA code editor.

Copy the Contents of a Row and Paste Values to the Next Empty Row in Another Worksheet Using Excel VBA

❺ Now hit the F5 button to run to code.

A dialog box will appear.

❻ Insert a cell range that you want to copy and hit OK.

Now you will see the output like the following screenshot in the “Output” worksheet.

Read More: Excel VBA: Copy Cell Value and Paste to Another Cell


Things to Remember

  • Press ALT + F11 to open the VBA editor.
  • To run VBA codes, press the F5 button.

Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


Conclusion

To sum up, we have discussed 3 methods to copy and paste values to the next empty row using Excel VBA. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

2 Comments
  1. Hello, is there anyway to insert text in several sheets at the same time, not just the “output”-sheet?

    • Hi Michelle,
      You can try the following piece of code:

      Sub PasteAcrossSheets()

      Dim arr(3)

      i = 0
      For Each Worksheet In ActiveWorkbook.Sheets
      Worksheet.Activate
      arr(i) = ActiveSheet.Name
      i = i + 1
      Next

      yy = ActiveWindow.RangeSelection.Address
      Set xx = Application.InputBox(“Insert a range:”, “Microsoft Excel”, yy, , , , , 8)
      If xx Is Nothing Then Exit Sub

      mm = Application.ScreenUpdating
      Application.ScreenUpdating = False
      xx.Copy

      Sheets(arr).Select

      Range(“G5”).Select
      ActiveSheet.Paste

      Application.CutCopyMode = False
      Application.ScreenUpdating = mm

      End Sub

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo