Pasting range in next empty cell causes run-time error 424: 'Object Required'

excel vba dynamic range based on cell value
select range based on cell value vba
excel vba if cell range
vba convert cells to range
excel vba find range of cells with values
copy range with variable vba
pass variable in range vba
get cell range vba

I copied a range from one worksheet into the next empty row in another worksheet through below code. But this raise an exception

run-time error 424; 'Object Required'.

Sub copy()
Dim lr1 As Long
lr1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet1").Range("A1:A" & lr1).copy
End Sub

Sub paste()
Dim lr2 As Long
lr2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet2").Range("A" & lr2).Offset(1, 0).PasteSpecial.xlValues
End Sub

The error occurs when I run the "Paste" macro. I want to reiterate that the copied range does get pasted in the next empty cell, however this error message get displayed.

Which part of the "Paste" macro triggers this message?

When pasting just values, skip the clipboard:

Sub copy()
    Dim lr1 As Long
    lr1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

    Dim rng As Range
    Set rng = Worksheets("Sheet1").Range("A1:A" & lr1)

    Dim lr2 As Long
    lr2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
    Worksheets("Sheet2").Range("A" & lr2).Offset(1, 0).Resize(rng.Rows.Count).Value = rng.Value
End Sub

Special copy/pasting causing VBA Runtime error 424: Object , When making a macro for copy/pasting data one row down with special paste values Special copy/pasting causing VBA Runtime error 424: Object Required Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False Range("H3: L11"). Is there a way to collapse it automatically once I move to the next cell? 26. I copied a range from one worksheet into the next empty row in another worksheet through below code. But this raise an exception run-time error 424; 'Object Required'.

A single sub and modification:

Sub copypaste()
Dim lr1 As Long, lr2 As Long
lr1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet1").Range("A1:A" & lr1).copy
lr2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet2").Range("A" & lr2).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End Sub

run time error 424: object required error when copying and pasting , the ActiveCell.Cells.Select.Copy.ActiveCell.Offset(0, 1).Select.Paste Else. No matter what tweaks I make to the statement, I keep getting Run-time error '424': object required. Next ct. Range("B1").End(xlDown).Offset(4, 0).Select. For ct = 1 To 130 'Include the next 160 columns and find all empty cells Dim MyVariableName As Integer MyVariableName = ActiveSheet.Range("D6").Value This assumes you have a number (like 5) in cell D6. Now your variable will have the value. For simplicity sake of learning, you can remove or comment out the Option Explicit line and VBA will try to determine the type of variables at run time.

I've changed my code as per BigBen's correction to the following;

Sub copy()
Dim lr1 As Long
Dim lr2 As Long
lr1 = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
lr2 = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Sheet1").Range("A1:A" & lr1).copy
Worksheets("Sheet2").Range("A" & lr2).Offset(1, 0).PasteSpecial paste:=xlPasteValues
End Sub

@Scott Craner: I had two subs as I'm new to vba and it's a way to break down the code so that I can understand what the code is supposed to do, but I guess that can be done with comments.

Vba run-time error 424 object required when using copy after , I feel like my ranges are defined, but getting runtime error 424 when i get to the Copy After where the "warning" symbol is posted below: Copy Visible cells Lastrow = Range("A" & Rows. Paste "Email To:" Worksheets("Temp"). Item(Cl .Value) = Empty Next Cl For Each Ky In .Keys Ws.Range("A1"). In the next line for the “Date” data type variable also I have used the “Set” keyword to assign the value of the cell A1 value in this workbook (Wb) and in the worksheet “Data” (Ws). Set MyToday = Wb.Ws.Cells(1, 1) In the next line, we are showing the value of the “MyDate” variable value of cell A1 value in the message box in VBA.

The Complete Guide to Ranges and Cells in Excel VBA, These can cause confusion as they do similar things and can lead to confusion To do something with Range you must first specify the workbook and worksheet it belongs Using Range is useful for accessing the same cells each time the Macro runs. You can see more about it in the Copying and Pasting Cells section. Information Excess; 10 Advantages of a Flexible LED Screen; Upgrading to Windows 10? My Experience; The Best SEO Plan for 2020; Simple and Easy Way to Secure Your Wifi Connection

Create PowerPoint Presentations Automatically using VBA , So, here it is – How to Save TONS of Time by Using an Excel VBA Macro to Build Create a new worksheet and paste in all the charts you need for the presentation. Now I get a runtime 430 error- Class does not support Automation or does not I have a range of cells (formatted as tables) that need to be copied from a� A subsequent attempt to use RetVal as an object reference causes this error: Dim RetVal ' Implicitly a Variant. ' Default property is assigned to Type 8 Variant RetVal.

Tom's Tutorials for Excel: Color the active cell, row, or column , Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells. hi Tom. i pasted this code below to avoid changing the original cell colors but nothing happened on I get run-time error 424: object required. might be a very good reason why your workbook was set to Manual calculation mode in the first place. Most of the tasks we automate with VBA do NOT require us to select a workbook, worksheet, or range first. Let's look at a simple example of copying and pasting a range of cells from one worksheet to another. The macro recorder produces the following code when I record my actions to copy range B2:B8 on Sheet1, and paste it on Sheet2.

Comments
  • .PasteSpecial Paste:=xlPasteValues.
  • Why two different subs?
  • I get an application-defined/object-defined error on my side
  • @giveitaway my bad. Forgot the .Count on the rows.
  • Hi Scott, what does Resize(rng.Rows.Count) do?
  • When assigning values directly the source range and target range need to be the same size. Resize(rng.Rows.Count) makes the ranges the same size starting with Worksheets("Sheet2").Range("A" & lr2).Offset(1, 0) as the first cell and going down.
  • Just a teaching note. As you get into loops, avoiding the clipboard will save a lot of time. If one is only interested in the values then simply assigning the values is quicker than copy/paste. In this instance the difference is negligible, but compound that in a loop and it becomes noticeable.