How can we solve the Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range ?

How can we solve the Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range ?

Asked on October 26, 2018 in VBA.
Add Comment


  • 3 Answer(s)

              This code can be used to solve theRuntime Error 1004 “Application-defined or Object-defined error” when Selecting Range:

     
    Option Explicit
    Sub CopySheet1_to_PasteSheet2()
    Dim CLastFundRow As Integer
    Dim CFirstBlankRow As Integer
    Dim wksSource As Worksheet, wksDest As Worksheet
    Dim rngStart As Range, rngSource As Range, rngDest As Range
    Set wksSource = ActiveWorkbook.Sheets("Sheet1")
    Set wksDest = ActiveWorkbook.Sheets("Sheet2")
    'Finds last row of content
    CLastFundRow = wksSource.Range("C21").End(xlDown).Row
    'Finds first row without content
    CFirstBlankRow = CLastFundRow + 1
    'Copy Data
    Set rngSource = wksSource.Range("A2:C" & CLastFundRow)
    'Paste Data Values
    Set rngDest = wksDest.Range("A21")
    rngSource.Copy
    rngDest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    'Bring back to top of sheet for consistancy
    wksDest.Range("A1").Select
    End Sub
     
    
    Answered on October 26, 2018.
    Add Comment

    Use this code:

    Sub XLTest()
    Dim aValues(4)
      aValues(0) = "Test1"
      aValues(1) = "Test2"
      aValues(2) = "Test3"
      MsgBox "First the Good range set."
      aValues(3) = String(911, 65)
      Range("A1 : D1").Value = aValues
      MsgBox "Now the bad range set."
      aValues(3) = String(912, 66)
      Range("A2 : D2").Value = aValues
    End Sub
    
    Answered on October 26, 2018.
    Add Comment
    •   Macro is recorded at the worksheet level.
    •   Right click on the modules node on the VBA project window, click on insert module then paste our macro within the new module (make positive you delete the one recorded at the worksheet level).
    Answered on October 26, 2018.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.