VBA copy and paste keeps spitting out application/object errors

Question

I have several simple lines of code that should paste a section of data into the cell I selected in Sheet2:

Sub asdf()
    Sheets("Sheet1").Range("A1:D5").Copy
    Worksheets("Sheet2").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Which just gives me application or object-defined errors.

Note: I also tried using ActiveCell which just causes this:

What is stranger is that it worked beforehand. Maybe because of saving issues?

Try specifying a `Range` object on the destination sheet... Btw `Worksheets("Sheet2").Range("A1:D5").Value2 = Worksheets("Sheet1").Range("A1:D5").Value2` would be a lot more efficient — Spectral Instance, Jul 13 '22 at 22:00
thanks, but I need it to increment to the right every time, which why I will put `ActiveCell.Offset(1, 0).Select` in later. — idk, Jul 13 '22 at 22:10
[how to avoid Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Cameron Critchlow, Jul 13 '22 at 22:27
Instead of using "Worksheets" (this object doesn't have the "PasteSpecial" method), try "Selection". To do this you first need to "Select" the target sheet as well as the desired cell to paste. Or, as stated by @Spectral Instance, use a "Range" object. — ALeXceL, Jul 13 '22 at 22:53
Stay in the early-bound realm: whenever you type a dot after anything and there's no drop-down list of members shown, you're probably making a member call against `Variant` or `Object`, which means the call is late-bound (i.e. resolved at run-time) and `Option Explicit` cannot save you from a typo. When it happens, declare a local object variable with an explicit data type (like `As Worksheet` or `As Range`) and `Set` it to the object you were going to make that member call against - then make that call against your local variable instead, and you'll get compile-time check and IntelliSense. — Mathieu Guindon, Jul 13 '22 at 23:20
Side note, [Rubberduck](https://rubberduckvba.com) inspections can help with a lot of these issues. (It's a free & open-sourced VBIDE add-in I made with some friends, enjoy!) — Mathieu Guindon, Jul 13 '22 at 23:22
I tried `Sub thingimajig() Dim columnvalue As Long ActiveSheet.Range("A1:B10").Copy ActiveSheet.Cells(1, columnvalue).PasteSpecial Paste:=xlPasteValues columnvalue = columnvalue + 1 End Sub` Still give me error 1004 — idk, Jul 14 '22 at 00:29

VBasic2008 · Accepted Answer · 2022-07-14T01:14:05.180

Copy Range Values to 'the Active Cell' of Another Worksheet

Option Explicit

Sub CopyRangeValues()
    
    ' Define constants:
    ' Source (read (copy) from)
    Const sName As String = "Sheet1"
    Const srgAddress As String = "A1:D5"
    ' Destination (write (paste) to)
    Const dName As String = "Sheet2"
    
    ' Reference our workbook ('wb').
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Reference the source worksheet ('sws') and the source range ('srg').
    Dim sws As Worksheet: Set sws = wb.Worksheets(sName)
    Dim srg As Range: Set srg = sws.Range(srgAddress)
    
    ' Reference the destination worksheet ('dws').
    Dim dws As Worksheet: Set dws = wb.Worksheets(dName)
    
    ' 2.) To reference the active cell in the destination worksheet,
    ' previously make sure that the destination worksheet is active.
    ' 1.) To activate the destination worksheet, previously make sure
    ' that our workbook is active.
    
    Application.ScreenUpdating = False
    
    ' 1.) Make sure that our workbook is active.
    Dim awb As Workbook: Set awb = ActiveWorkbook
    If Not wb Is awb Then wb.Activate
    ' 2.) Make sure that the destination worksheet is active.
    Dim ash As Object: Set ash = wb.ActiveSheet ' could be a chart
    If Not dws Is ash Then dws.Activate
    
    ' Reference the destination first cell ('dfCell'), the active cell
    ' in the destination worksheet, using 'Application.ActiveCell'.
    Dim dfCell As Range: Set dfCell = ActiveCell
    
    ' Reference the destination range ('drg'), the destination first cell
    ' resized by the number of rows and columns of the source range.
    Dim drg As Range: Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)
    
    ' Copy (by assignment) the values from the source range
    ' to the destination range.
    drg.Value = srg.Value
    
    ' Activate the initial active sheet in our workbook
    ' (if it wasn't the destination worksheet).
    If Not dws Is ash Then ash.Activate
    ' Activate the initial active workbook (if it wasn't our workbook).
    If Not wb Is awb Then awb.Activate
    
    Application.ScreenUpdating = True
    
    ' Inform.
    MsgBox "Copied the values from the range '" & srgAddress _
        & "' in worksheet '" & sName & "' to the range '" & drg.Address(0, 0) _
        & "' in worksheet '" & dName & "' of the workbook '" & wb.Name & "'.", _
        vbInformation
    
End Sub

VBA copy and paste keeps spitting out application/object errors

1 Answers1

Copy Range Values to 'the Active Cell' of Another Worksheet