0

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.

STLDev
  • 5,950
  • 25
  • 36
Irina
  • 3
  • 1
  • 1
  • 3
  • 1
    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 May 15 '18 at 16:05
  • 2
    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 May 15 '18 at 16:06
  • See [here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) for more detail on common errors when trying to get the "last" row or column. – David Zemens May 15 '18 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: https://stackoverflow.com/questions/17281872/error-pastespecial-method-of-range-class-failed/17282648#17282648 – David Zemens May 15 '18 at 16:08
  • Thank you Bruce, I already tried to do it. The same result. – Irina May 15 '18 at 16:15
  • David, I use Offset, because I need to paste values every time in a new column. And thank you, for the links. I will see the them. – Irina May 15 '18 at 16:16

1 Answers1

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)

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
  • 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. – Irina May 15 '18 at 16:26
  • 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. – Darren Bartrup-Cook May 16 '18 at 07:56