CODEWITHSUNDEEP
excelvba
Mirza
Mirza

Reputation: 23

Why am i getting this error with copy/paste?

I am getting an error

Runtime error 1004: Application defined or Object defined error

in my vba code. Could you please help me correct it?

Sub INPUT_DATA()
    ' INPUT_DATA Macro
    ' Application.ScreenUpdating = False
    Application.EnableEvents = False
    Sheets("Input").Select
    If Range("D55").Value = 0 Then
        Range("B2:AI52").Select
        Selection.Copy
        Sheets("Database").Select
        ActiveSheet.Range("A2").End(xlDown).Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End If
    Sheets("Input").Select
    MsgBox ("File has been updated. DO NOT PRESS UPDATE again, as it will enter the same data once again")
End Sub

Upvotes: 0

Views: 65

Answers (1)

Domenic
Domenic

Reputation: 8104

You didn't say which line is causing the error, but it looks like it's likely this line...

ActiveSheet.Range("A2").End(xlDown).Offset(1, 0).Select

It starts at A2, and then it goes down until it finds the last used row. However, if there aren't any used rows below A2, it finds the very last row in Column A. Then it tries to offset to one row below it, which doesn't exist, and hence the error. Instead, you can find the next available row as follows...

ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

Although, there's no need to do all of that selecting. It's very inefficient. So your macro can be re-written as follows...

Option Explicit

Sub INPUT_DATA()

' INPUT_DATA Macro

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = Worksheets("Input")

    Dim destinationWorksheet As Worksheet
    Set destinationWorksheet = Worksheets("Database")

    With sourceWorksheet
        If .Range("D55").Value = 0 Then
            .Range("B2:AI52").Copy
            With destinationWorksheet
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
            End With
        End If
    End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

    MsgBox ("File has been updated. DO NOT PRESS UPDATE again, as it will enter the same data once again")

End Sub

Upvotes: 1

Related Questions