Compile error - Excel VBA

Question

Hello all,

Microsoft Excel version: Microsoft Professional + 2016

VBA Sub code: Clear_Existing_Data_Before_Paste ( )

I used below code a few times without any problems. This time I got a "Compile error: Invalid use of property" notification.

I`ve not made any changes to the coding, does somebody know how to write the code that it becomes more stable and less error-prone?

Below are two screenshots, first screenshot is of the notification and the second screenshot marked the error. 

Screenshot 1:

Screenshot 2:

Any idea is welcome, thanks for your effort.

Kind Regards,

Erik

Answer 1

If you pass an argument to the Copy method, the argument must be a Range object.

https://docs.microsoft.com/en-ie/office/vba/api/excel.range.copy

You try to pass the Delete method as argument, hence the error.

I can only guess: Move the line wsN.Delete below wsDest.Range("A1")

Andreas.

Answer 2

Hello Erik

I am V. Arya, Independent Advisor, to work with you on this issue. Was it working earlier with WsN.Delete in that place?

I tried to replicate the error. I didn't get that.

Answer 3

Hello Andreas, thanks for your reply. I made the change as you advised, unfortunately the code does not copy the data in the worksheet anymore. Sadly it does not work. Still, thanks for trying to find a solution. 

Have a nice day!

Kind Regard

Erik

Answer 4

Hello V. Arya, thanks for reaching out to me.

It was working before, I run the code this afternoon and all of the sudden I got a Compile Error.

I changed the formula as @Andreas proposed, the Compile error is gone, but the formula does not work paste the new data in the worksheet anymore. Do you have a other alternative for me, a code that I can enter that copy data from workbook (1), removes existing data in workbook (2) and paste new data in workbook (2)?  

Please let me know if you want me to forward anymore details.

Thanks for your help.

BR

Erik

Answer 5

Try using following in place of WsDest.Range("A1")

WsDest.Range("A1").PasteSpecial xlAll

Your sequence has to be

'First clear the destination sheet

WsDest,Cells.Clear

Then put your copy line followed by paste line

WsDest.Range("A1").PasteSpecial xlAll

Then you can use wsN.Delete

Nothing should be between copy and paste statement