Error Code 1004 When Copy/Pasting VBA Cant Solve

Question

I have been looking at this for 5+ hours not being able to find a correct solution. This isn't my main deal just what I do in work to help out.

Basically I am copying from a sheet that has filtered rows to another sheet and placing it at the last row in column A to paste.

This worked perfectly find before I did some changes and now it's completely broken, any help be gratefully appreciated, here is the broken lump of spaghetti code....

Sheets("Working Sheet").Select
Selection.Copy
Sheets("Sent Items").Select

Dim LastRow As Long
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

Range("A" & LastRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("K:K").EntireColumn.AutoFit
Sheets("Sent Items").Select

It causes an error 1004 saying the size needs to be the same??? The paste causes the error. Any help is good have been looking for the answer.

Size being the same usually is due to mergerd calls from the original ("Copy") part - are there any? — Jeremy, Dec 01 '16 at 14:02
There are no merged cells on the copy, read that in another post thanks for the reply though — D Silva, Dec 01 '16 at 14:06

score 1 · Answer 1 · edited May 23 '17 at 12:13

1

Since you are copying filtered rows, it's always a good practice to use the SpecialCells method.

See the refactored code below. Also, always best to avoid using select and work directly with objects.

Dim wsWorking as Worksheet
Set wsWorking = Sheets("Working Sheet")

With wsWorking

   .Select
   Selection.SpecialCells(xlCellTypeVisible).Copy

End With

Dim wsSent as Worksheet
Set wsSent = Sheets("Sent Items")

With wsSent

    Dim LastRow As Long
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

    .Range("A" & LastRow).PasteSpecial

    .Columns("K:K").EntireColumn.AutoFit

    .Range("A1").Select 'to make sure you end up on that sheet

End With

edited May 23 '17 at 12:13

Community

1
1

answered Dec 01 '16 at 14:06

Scott Holtzman

27,099
5
37
72

This definitely neatened up this section of code but still isn't working. Now it is saying that >Sheets("Working Sheet").Selection.SpecialCells(xlCellTypeVisible).Copy Object Doesnt support Property or Method – D Silva Dec 01 '16 at 14:16
@DSilva - Try now. I would advise against `Selection` unless you absolutely have to. It's better to define the range outright. But my edits should get you around that for now if you really need to use the selected raise at beginning of macro. – Scott Holtzman Dec 01 '16 at 14:23
1

@ScottHoltzman, this assumes that at the macro running `"Working Sheet"` sheet is the _active_ one, so that `rngSelection` actually captures _that_ very sheet selected cells. Since we have no clues from OP as to how identify this `Selection`, I think it's safer to leave a `Worksheets("Working Sheet").Select` before using `Selection` object. – user3598756 Dec 01 '16 at 16:11

score 1 · Answer 2 · answered Dec 01 '16 at 16:06

you could refactor your code as follows:

Worksheets("Working Sheet").Select
Selection.SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("Sent Items").Cells(Rows.Count, 1).End(xlUp).Offset(1)

or, if you are interested in pasting values only:

Dim area As Range

Worksheets("Working Sheet").Select
For Each area In Selection.SpecialCells(xlCellTypeVisible).Areas
    Worksheets("Sent Items").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(area.Rows.Count, area.Columns.Count).Value = area.Value
Next area

Error Code 1004 When Copy/Pasting VBA Cant Solve

2 Answers2