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 wth 3 examples.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
3 Examples: Copy and Paste Values to Next Empty Row Using Excel VBA
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.
❸ 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.
❺ 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.
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.
❺ 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.
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.
❺ 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.
Things to Remember
- Press ALT + F11 to open the VBA editor.
- To run VBA codes, press the F5 button.
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. And please visit our website Exceldemy to explore more.
- Excel VBA: Copy Range to Another Workbook
- [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
- How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)
- Disable Copy and Paste in Excel without Macros (With 2 Criteria)
- How to Copy Excluding Hidden Rows in Excel (4 Easy Methods)
- Excel VBA to Copy Rows to Another Worksheet Based on Criteria
- How to Use VBA to Paste Values Only with No Formatting in Excel