Help with code to paste copied data into a destination workbook

Hi,

I have struggled to modify existing code I've found around the internet so I've started to make my own.

What I'm trying to do is to copy a daily diary log at the end of the week after it's been checked. So the Diary Log.xlsm file will already be open and there will be a button for the supervisor to click to archive the data to a Master workbook. There will be 50 of these files and various different people checking the data is correct and then archiving to the same Destination workbook. The Destination workbook will be closed initially, opened with the code and the copied range will then need to be pasted into the Destination workbook in the next empty row.

So far I have managed to open the Destination workbook and copy the range which can be any number of rows. I am now stuck on the code to paste it into the Master workbook. Please could you look at my code and tell me where I'm going wrong. The code under 'Paste to destination workbook gives a Run-time error '1004' Application-defined or object-defined error message

The code will need to paste into Row B for the first export (Headings in Row A) and then the next available row from then on. Also there are named ranges in the Daily Log file which I don't want to copy over as that produces an 'error' on the second export with a pop-up saying the name already exists on the target worksheet. (Found this out on a previous attempt to modify someone else's code). Will a Paste Values option avoid copying the named ranges?

Any pointers on how I can do the following would also be helpful....Once the data has been pasted into the destination workbook I want to clear the data from row B14:014 down to B100:O100 as a range in the Diary Log file, save and close the destination file and also save but not close the Diary Log file.

The code I have so far is:

Please Login or Register  to view this content.

I have attached the Diary Log.xlsm file and the Destination.xlsm files to give you an idea of what I'm trying to do.

Thanks for any help you can give me...

Can anyone help tell me where I'm going wrong with the line of code beneath 'Paste to destination workbook. I need the code to find the last row and then paste the copied data into the row beneath it?

Ok, no problem, I'll try somewhere else

Hi Norie, yes to the first empty cell in column A...thanks for replying