Copy and Paste from many workbooks into one worksheet

[Paul Morgan]

Hi There,

I am trying to copy and paste ranges from many workbooks into a seperate worksheet

The workbooks are in a forlder called C:\Invoices

The ranges I would like to copy and paste to the seperate worksheet is

Sheets("Invoice").Range ("M3,D13:H13,E17,M13")

I would like to be able to loop through all the workbooks in the folder, and have the ranges pasted one row then the next row ect.


Is there a way this can be performed?

[davesexcel]

Here's some code I have found, with some customizing added

Please Login or Register  to view this content.

This code originated from this site

This code is available from many sites.

[Paul Morgan]

Thanks Dave,
I thought the code was working great, but then noticed the data being transferred does not always match the invoice, such as the invoice # was in column A (M3) from the Invoice sheet but the amount in column E was showing a different amount (M40) from the invoice sheet ( the amount was coming from another invoice), it had worked well with a small sample, but when I used it for the main folder, they don't match

Does anybody know of a better way, to transfer the ranges from the invoices to the summary sheet?

[davesexcel]

Thanks Dave,
I thought the code was working great, but then noticed the data being transferred does not always match the invoice, such as the invoice # was in column A (M3) from the Invoice sheet but the amount in column E was showing a different amount (M40) from the invoice sheet ( the amount was coming from another invoice), it had worked well with a small sample, but when I used it for the main folder, they don't match

Does anybody know of a better way, to transfer the ranges from the invoices to the summary sheet?

The most obvious suggestion is that....

Some of the files have ranges that are blank, so when they are transferred to the summary sheet, it pastes a blank,
then when the next sheet is looped, the code will paste the range into the next empty cell, since the previous workbook had a range that was blank, the next workbook opened will paste into the next blank cell, therfore escalating into an inacurate entry

[Paul Morgan]

Thanks Dave, That is exactly the problem, with lots of blank cells in invoices , as I have been fixing them and running the macro, data in starting to match