Copy range of data from all workbooks in folder and paste to multiple sheets in new book

[Brontosaurus]

Hello, I've researched many similar examples but none that I've found meet my needs and my VBA skills aren't good enough to adapt. Please would someone kindly help. I have a folder containing workbooks which I want to copy a specific range of data from one named worksheet and then paste into a newly created worksheet in a summary workbook where the new worksheet is labelled with the source workbook name.

The source data folder contains a variable number of workbooks (probably between 5 and 25) so the new summary workbook would contain a variable number of worksheets.

The source workbook would be named eg. 201905TV21 (year 2019, week 05, manager TV, project 21) and this would become the label for the new worksheet into which the range of data would be copied. This is so that the origin of the data is confirmed in the summary workbook.

Could someone help me to get on the right track with this please?

Thanks,

Jess

[Pepe Le Mokko]

Does this help ? http://www.rondebruin.nl/win/addins/rdbmerge.htm
( there is also code version at http://www.rondebruin.nl/win/section3.htm)

[Brontosaurus]

Thank you for your suggestion. I had already looked at this but all of the versions Ron offers combine the data into one worksheet. None of the examples I've found copy the data from the source worksheet range and paste it into a new worksheet.

[Brontosaurus]

Hi, I've copied/modified this code which I found. It runs but ...

1. it doesn't copy the data across.
2. I need it to paste the data into separate worksheets in the target workbook. That is;

source workbook 1 data copied to target workbook x worksheet 1
source workbook 2 data copied to target workbook x worksheet 2
source workbook 3 data copied to target workbook x worksheet 3 etc.

Please would someone help with the necessary modification to the code?

Thanks,

Jess

Please Login or Register  to view this content.

[Brontosaurus]

Is there a chance someone may help with this please or let me know if I should present it differently?

Thanks,

Jess

[Alf]

You could try a macro like this and see if that works for you.

Please Login or Register  to view this content.

As this macro adds a new sheet for each file it reads data from I would suggest that you put the macro in a file with only 1 sheet.

Alf

[Brontosaurus]

Sorry, I've only just seen this reply. Thank you very much.

[Alf]

No problem

You are welcome and thanks for feedback and rep

Alf