0

I'm gettin error 1004 while running a macro:

Blockquote"Run-time error '1004': You can't paste this here because the Copy area and paste area aren't the same size. Select just one cell in the paste area or an area that's the same size, and try pasting again."

I'm selecting just one cell, don't know why the error is happening. The Macro is:

Range("AD28").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.Offset(0, 6)).Select
Selection.Copy

Sheets("DadosPart").Select

Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False   '(HERE IS THE ERROR)

Does someone knows how to fix it?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
ngbruno
  • 1
  • 1
  • 1
    `Debug.Print Selection.Address` - what cell are you trying to paste into? And what is the original range you are trying to copy? – BigBen Nov 05 '20 at 13:52
  • 3
    Also, you may want to see [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – BigBen Nov 05 '20 at 13:52
  • 1
    "I'm selecting just one cell" No, you're using `Selection.End(xlDown)` which selects everything below the active cell that has data. Put a breakpoint at the beginning, watch the selection in the spreadsheet (don't click on anything in the spreadsheet or you'll change the selection) and see which cells get selected. – D Stanley Nov 05 '20 at 14:04
  • 2
    If `Selection.End(xlDown)` selects the very last cell you get this error because you copied more than one cell. You cannot paste a range in the very last cell because this exceeds the number of rows an Excel sheet has. – Pᴇʜ Nov 05 '20 at 14:06
  • @BigBen i don´t know how to use and where to put these Debug.Print Selection.Address. The range is variable, depends in what sheet I'm running the macro. – ngbruno Nov 05 '20 at 14:37
  • Before `Selection.Copy` and before `Selection.PasteSpecial` and then check the immediate window (Ctrl+G). – BigBen Nov 05 '20 at 14:38
  • @DStanley I tought the:ActiveCell.Offset(1, 0).Select after Selection.End(xlDown) would select just one cell below it. I have recorded I'm running the macro in debug mode (F8). But doing that, the error doesn´t occurs. [link] (https://www.youtube.com/watch?v=zPctL4EnXGQ) – ngbruno Nov 05 '20 at 14:40
  • @BigBen the debug before selection.copy returns: $AD$28:$AJ$35 and the other one returned: $B$56 – ngbruno Nov 05 '20 at 14:42
  • @BrunoGarcia did you check my answer below? – Pᴇʜ Nov 05 '20 at 14:45
  • Yes, @Pᴇʜy! I was just writing for you. I don´t think this is the case, It doesn´t select the very last cell. The range selected is (AD28:AJ35). Now I´ll try the code you wrote, but I´m a newbie. I don´t know if I understood everything. The worksheet where I run the macro always change, how can I define my sheet name if it changes every once I run the macro. – ngbruno Nov 05 '20 at 14:50
  • @BrunoGarcia [`Selection.Offset`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.offset) does not change the size of the selected range. It just moves the entire selection by the number of rows/columns – D Stanley Nov 05 '20 at 14:57
  • @BrunoGarcia but this `Selection.End(xlDown).Select` can select the very last cell. – Pᴇʜ Nov 06 '20 at 07:59

1 Answers1

0

If Selection.End(xlDown) selects the very last cell you get this error because you copied more than one cell. You cannot paste a range in the very last cell because this exceeds the number of rows an Excel sheet has.

The method to finde the last used row that you used (with xlDown) is not reliable. To find the last used row use something like:

Cells(Rows.Count, "B").End(xlUp) 'find last used row in column B

Also don't use .Select this is a very bad practice.

Dim wsSource As Worksheet 'define your sheet
Set wsSource = ThisWorkbook.Worksheets("Sheet1") 'adjust your sheet name

wsSource.Range(wsSource.Range("AD28"), wsSource.Cells(wsSource.Rows.Count, "AD").End(xlUp)).Resize(0, 7).Copy

Dim wsDest As Worksheet 'define your sheet
Set wsDest Source = ThisWorkbook.Worksheets("DadosPart")

wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • If you want to run this on different sheets then use `Set wsSource = ThisWorkbook.ActiveSheet` – Pᴇʜ Nov 06 '20 at 07:58