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
– BruceWayne, Commented May 15, 2018 at 16:05
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
– David Zemens, Commented May 15, 2018 at 16:06
See here for more detail on common errors when trying to get the "last" row or column. — David Zemens
– David Zemens, Commented May 15, 2018 at 16:07
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
– David Zemens, Commented May 15, 2018 at 16:08

Darren Bartrup-Cook · Accepted Answer · 2018-05-15 16:22:16Z

2

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)

edited May 15, 2018 at 16:22

answered May 15, 2018 at 16:16

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Irina Over a year ago

Thank you Darren, it works! Only one thing I can't understand is why my primary code works in other spreadsheet. In this case I just changed range and sheets name.

Darren Bartrup-Cook Over a year ago

it depends on the data on the sheet. If you manually select cell A2, press Ctrl+Right and then try and move one more cell to the right you should see why your code failed, or succeeded as the case may be.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related