Mrinmoy Roy
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.
Read More: Excel Formula to Copy Cell value to Another Cell
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.
Read More: Excel VBA: Copy Cell Value and Paste to Another Cell
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.
Read More: How to Copy a Cell in Excel Using Formula(7 Methods)
Things to Remember
- Press ALT + F11 to open the VBA editor.
- To run VBA codes, press the F5 button.
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. And please visit our website Exceldemy to explore more.
Related Articles
- 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
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