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?

CodePudding user response：

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