Method 1 – Copy a Range of Cells and Paste Values from the Next Empty Row in Excel Using VBA Code
In this particular example, we will copy row 11 and paste it to the next blank row.
Steps:
- Press Alt + F11 to open the VBA code editor.
- Create a new Module from the Insert tab.
- 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 code in the VBA editor and save it.
- Hit the Run Sub button or press F5 to run the code.
Read More: Excel VBA to Copy Only Values to Destination
Method 2 – Copy the Contents of a Row and Paste Values to the Next Empty Row in Excel VBA with a Condition
- Open the VBA code editor by pressing Alt + F11.
- Go to Insert and choose Module.
- Input the following VBA code into the module:
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- Save the code in the VBA editor.
- Press F5 to run the code.
- 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.
Read More: Excel VBA: Copy Row If Cell Value Matches
Method 3 – Copy the Contents of a Row and Paste Values to the Next Empty Row in Another Worksheet Using Excel VBA
- Press Alt + F11 to open the VBA editor.
- Click Insert and choose Module.
- Insert the following VBA code into the module.
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- Press Save or Ctrl + S.
- 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.
- You will get the output in the “Output” worksheet.
Read More: Excel VBA: Copy Cell Value and Paste to Another Cell
Download Practice Workbook
You can download the Excel file from the following link and practice along.
Related Articles
- How to Paste From Clipboard to Excel Using VBA
- How to Copy Visible Cells Only without Header Using VBA
- How to Use VBA PasteSpecial for Formulas and Formats in Excel
- How to Use VBA PasteSpecial to Keep Source Formatting in Excel
- How to Use VBA to Paste Values Only with No Formatting in Excel
- How to Copy Multiple Rows in Excel Using Macro
- How to Autofilter and Copy Visible Rows with Excel VBA
- VBA Paste Special to Copy Values and Formats 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