Adapt my code which creates a summary document

[lrm75uk]

I have been trying to create a summary document of approximately 10 other workbooks which will all be stored in the same folder.
The workbooks will be e-mailed in to a central location from different sources.
The filepath of this folder where they will be saved to will change in each of the central locations.
I have a attached an example of a the document that will be being summarised (they will all be exactly the same) and i have attached a code that i have located on a forum which does seem to work although i would like it to be adapted if possible.

I am not vba literate so would really appreciate your expertise.
I just want the 'sheet 1' of each workbook to be summarised on the 'Master' sheet of the Summary Workbook.
The 'Master sheet' will just show all the results continuosly down the page.

Currently my code deletes the contents of the 'Master Sheet' before filling it with the new data, which is great but i want it to leave Columns A:B and Rows 1:5 completely untouched when deleting old data or filling it in with new data.

Attachment: Book 3.xls - this is what all the documents will look like

Please Login or Register  to view this content.

Thankyou in advance if you can help.

[StephenR]

You've marked your thread as solved - is this a mistake?

[lrm75uk]

My mistake apologies and thanks..

[StephenR]

If I understand correctly, try adjusting two lines below:

Please Login or Register  to view this content.

[lrm75uk]

Hi Stephen, thanks for the reply.
i tweeked the first line you sent to

Please Login or Register  to view this content.

this seemed to work o.k but the code now has an error which stops at the second code you sent.
It says 'Run time error '1004' - The information cannot be pasted because the copy area and the paste area are not the same size and shape.
Would you have any suggestions?

If it makes your life easier then we can use from column A on the Master sheet as long as the first 5 lines are left untouched as this will contain other info.

thanks again
Lee

[StephenR]

Lee - thanks for spotting my deliberate mistake! The other line seems to work for me. All I did in that line is change the "b" to a "c" so I'm not clear how that could make a difference. Can you attach a sample of the master sheet?

[lrm75uk]

Hi Stephen,
heres the Master sheet.
Its not complete but you'll get the idea..

thanks for your persistance

[StephenR]

In the attachment you have this line of code. I think the IV is a typo - it should be I? If you're using xl2003 IV is the last column so it can't fit in your data, hence the error message.

Please Login or Register  to view this content.

[lrm75uk]

Hi Stephen,
The macro is now working great thanks to your help and advice. thanks for your support.

Do you know, Is there any reason why the date column wont keep the date format from the source worksheet? eg01/01/2010) - if i format the cells for the date on the Master sheet and then run the macro afterwards to update the data it still doesnt keep the date formating.

I have attached a copy of the Master Summary sheet that does now work for everyones reference. (minus my little date formating question).

cheers Stephen

[StephenR]

Glad it nearly all works now. Not sure what causes that problem. You could try changing the paste special parameter:

Please Login or Register  to view this content.

[StephenR]

Double posted and can't see a way to delete post.

[lrm75uk]

Works a treat.
I cant thank you enough for your expertise and persistance!!
That was the last piece of the puzzle.

I am going away a happy man!