Excel VBA Error 1004: Application-defined or object-defined Error

Question

I have a macro that copies data from one sheet and pastes values to another. Everything seems good but I received the error:

Run-time Error 1004: Application-defined or object-defined Error

I simplified the code for your convenience because the whole macro is a set of similar codes:

Sub CopyPaste()
  Sheets("Primary").Select
  Range("A1").Select
  Selection.Copy
  Sheets("Result").Range("A2").End(xlToRight).Offset(, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
End Sub

The debugger shows the problem with the row:

Sheets("Result").Range("A2").End(xlToRight).Offset(, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I really don't understand what's wrong here. Almost the same code works with another Excel spreadsheet.

I went through similar questions here but couldn't find any that help me. So, maybe it's easy for VBA Professionals but not for me. It's only my second week using VBA, for this reason any help is very appreciated.

In your code you're actually running, did you split the SkipBlanks on to two lines, as you demonstrate here? Also since you're just doing the values, try this instead of copy (your whole sub can be one line): Sheets("Result").Range("A2").End(xlToRight).Offset(0,1).Value = Sheets("Primary").Range("A1").Value — BruceWayne
What is the value of Range("A2").End(xlToRight).Address? It's possible you've reached the literal end (last column) of the worksheet, therefore the Offset(,1) will fail. — David Zemens
See here for more detail on common errors when trying to get the "last" row or column. — David Zemens
And you don't need to use Copy / PasteSpecial if all you're doing is values, just do a direct value assignment: stackoverflow.com/questions/17281872/… — David Zemens

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2018-05-15T16:16:10

As @DavidZemans said in comments...

If row two on the Result sheet is empty (or completely full) then xlToRight will get to column XFD and then try to offset one column off the sheet which causes the error.

It's better to start at XFD and look to the left - if the row is empty it will return the first column. So:
sheets("Result").cells(1,columns.Count).end(xltoleft)

Also, as you're only copying the value you can just say "set this cell value to equal that cell value".

Sub CopyPaste()

    Sheets("Result").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1) = Sheets("Primary").Range("A1")

End Sub

Edit: Nearly forgot - if column XFD is populated then this could also muck up (add a value to that column and press Ctrl+Left and it selects column A)

Excel VBA Error 1004: Application-defined or object-defined Error

1 Answers