Sometimes when I run the code it works, other times I get Run-Error '1004' and I don't know why?

Question

First of all I'm a beginner with VBA so I don't understand a lot. I tend to record my macros and then slightly modify them.

I get the following Error 1004 message

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.

   Private Sub TransferExpenses_Click()
    
        ThisWorkbook.Activate   'Transfer ExpenseImport Data over to CMiCExport Tab
        Sheets("ExpenseImport").Select
        Range("A1:AE1", Selection.End(xlDown)).Select
        Selection.Copy
        ThisWorkbook.Activate
        Sheets("CMiCExport").Select
        Sheets("CMiCExport").Range("A1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Range("A1").Select
        Sheets("CMiCExport").Paste
    
        MsgBox Title:="Expenses Imported Successfully!", Prompt:="The data for your expenses was verified and transferred to the CMiCExport Tab. Please double check column C -Job & Scope- and revise the .XXDefault entries."
    
   End Sub

I'm basically just trying to copy data from one sheet "ExpenseImport" to "CMiCExport" on the next blank row. The data will always be from column A to AE since it's mapped, but the rows will always vary depending on the amount of entries for that particular week. When I step into and run the code using the "F8" it works just fine, but when running the code using an active control, it fails. Can someone help me?

Try this - Remove the last select call, and for the paste use this: `ActiveCell.Offset(1, 0).PasteSpecial` — Mike67, Aug 13 '20 at 02:14
You can step through the macro code in the VBA editor to see what is happening line by line. My guess is that Selection.End... is a left over from your recording that you don't need, and the following is recovering from that action. I find that stepping through is invaluable in debugging this type of problem. — Nikkorian, Aug 19 '20 at 14:15

score 0 · Answer 1 · answered Aug 19 '20 at 14:00

I think it´s because the blank cells. Try this:

Private Sub TransferExpenses_Click()
     ThisWorkbook.Activate   'Transfer ExpenseImport Data over to CMiCExport Tab
     Sheets("ExpenseImport").Select
     Range("A1:AE1", Selection.End(xlDown)).Select
     'Avoid the copy if all cells are blank
     If Application.WorksheetFunction.CountBlank(Selection) = Application.WorksheetFunction.CountBlank(Selection) Then
         Exit Sub
     End If
     Selection.Copy
     ThisWorkbook.Activate
     Sheets("CMiCExport").Select
     Sheets("CMiCExport").Range("A1").Select
     Selection.End(xlDown).Select
     ActiveCell.Offset(1, 0).Range("A1").Select
     Sheets("CMiCExport").Paste
 
     MsgBox Title:="Expenses Imported Successfully!", Prompt:="The data for your expenses was verified and transferred to the CMiCExport Tab. Please double check column C -Job & Scope- and revise the .XXDefault entries."
End Sub

Sometimes when I run the code it works, other times I get Run-Error '1004' and I don't know why?

1 Answers1