VBA: Copy & paste area not the same size error

[Barraka]

Hi there,

I have the following code:

Sub test()
Sheets("try1").Select
Range("e2:e" & [e65000].End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Copy Sheets("destination").Range("e2:e999").End(xlDown)
End Sub

What it does is copy all the non-empty cells from column E to a different sheet, at the same column.
It gives me an error message (error '1004', copy and paste area are not the same size). However this only happens if the destination column is empty. If I have a value in row 3, the macro works well (but it erases the value I had in E3).

Is there a workaround that problem?

Thanks for your help

 

[jeffreybrown]

Hello & Welcome to the Board,

Try this...

Sub test()
    With Sheets("try1")
        .Range("E2:E" & .Range("E" & .Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Copy _
            Sheets("destination").Range("E2")
    End With
End Sub

 

[Barraka]

Thanks for your response,

I tried your code and it does work: when the destination column is empty, there is no more error message.
However, I'd like the macro to paste under the last non-empty cell, because it will run several times.
In other words, when it is run the first time it needs to paste in the empty column (what your code does), and when it is run the other times it needs to paste under the last cell (what my code does with the Range(xx).End(xlDown) ).

Is there a way to have the macro run and work under both circumstances?

Thanks

 

[excel.vba]

I am not sure if this is the solution you are looking for.
I made use of the code pasted previously and just added an if else statement..Hope it helps

Sub test()
With Sheets("Sheet1")
If (Sheets("Sheet2").Range("E2").Value = " ") Then
.Range("E2:E" & .Range("E" & .Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Copy _
Sheets("Sheet2").Range("E2")
Else
.Range("E2:E" & .Range("E" & .Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Copy _
Sheets("Sheet2").Range("E" & .Rows.Count).End(xlUp)
End If
End With
End Sub

 

[jeffreybrown]

You might try...

Sub test()
    Dim wsSrc As Worksheet
    Dim wsDest As Worksheet
    Set wsSrc = Sheets("try1")
    Set wsDest = Sheets("destination")
        
        Application.ScreenUpdating = False
        
        With wsSrc
            If wsDest.Range("E2").Value = "" Then
                .Range("E2:E" & .Range("E" & .Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Copy _
                    wsDest.Range("E2")
            Else
                .Range("E2:E" & .Range("E" & .Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Copy _
                    wsDest.Range("E" & Rows.Count).End(xlUp).Offset(1)
            End If
        End With

        Application.ScreenUpdating = True
    
End Sub

 

[Barraka]

Hi,

Thanks for the response,

I managed to get the first code suggested to work, with a slight modification:

Sub test()

Sheets("try1").Range("e2:e" & Sheets("try1").Range("e65000").End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Copy _
    Sheets("destination").Range("e65000").End(xlUp).Offset(1, 0)

End Sub

This code works just fine, so thanks for that jeffrey.
Is there a way to further improve the code so that it only copies the values of the cells, without format? I tried modifying the above example with the PasteSpecial methode, but couldn't get it to work.

Thanks

 

[Barraka]

Actually I got my code to work, it wasn't so hard after all:

Sub test()
Sheets("try1").Range("e2:e" & Sheets("try1").Range("e65000").End(xlUp).Row).SpecialCells(xlCellTypeConstants, 23).Copy
Sheets("destination").Range("e65000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End Sub

Thanks again

 

[Barraka]

Oh well, it was too good to be true.

The above code works, but I just found out that I have an error message if the source cells are empty.

Does anyone know how to figure that out?

 

[mirabeau]

The usual way of handling errors in SpecialCells approaches when there are no such special cells is to put

On Error Resume Next

in an appropriate (before the error-causing line) part of the code.

Sometimes other approaches might be better, sometimes not.

 

[Barraka]

Unfortunately the line

On Error Resume Next

does prevent the error from happening, but it pastes in the destination cells the values from a previous copy operation.

I think what I need to do is get the macro to skip the operation if the source cells are empty.