Code imports new sheet instead of updating existing & overwrites existing data

Hi,

I am hoping to get some help with some VBA code I am attempting to use to import data from multiple Excel workbooks.

Background:
There are approx 6 Excel 2003 audit workbooks that all have 7 identical worksheets, although they contain varying amounts of audit data (no more than 400 records in each). I am wanting to import this data to a kind of master file which is set up for dashboards. The master file has the same 7 worksheets (plus other ones not related to this) which need to be updated.

As an example, I need to to transfer the data from worksheet "Audit Data" in workbook 1 through workbook 7, to worksheet "Audit Data" in the master file. As they will all have different data, I need it to not replace the data as it copies from each workbook, but to place it in the next available row.

In addition, I require the information to be imported starting at D2 in each worksheet because of other formulas in place in the workbooks. I can work around this though, if it is an issue.

After hunting around for a while, I found an existing piece of code on this forum which is great and is nearly perfect but I'm having trouble adapting it (aside from worksheet names etc).

Please Login or Register  to view this content.

I have read dozens of posts on this topic, but this code really best suits my needs and I am really hoping to get it working.

Problem:

Please Login or Register  to view this content.

I think this is my issue. This creates new worksheets in the master file for each of the worksheets imported. Eg. "Audit Data (2)", "Audit Data 3" etc.

I have tried to correct this using my own VBA knowledge and by using other pieces of code I have found, but none seem compatible with the rest and my splicing causes errors. In its current form, the code runs smoothly without errors, so its not an error with the code itself, its just not suitable to my needs.

If anyone could help correct this bit of code for me, I would be incredibly thankful. I'm hoping I have provided the information you will need, but if not, please just let me know.

Thanks for offering a great service.

Regards,

James