Merging 1 worksheet from multiple workbooks into a master workbook

Hi All,

I'm trying to take a single worksheet from a workbook and merge them all into one workbook.
In that master workbook I'm looking to have each of the worksheets on different tabs and the tab names as the original workbook name.

So if I have Workbook1, Workbook2, Workbook3, Workbook4 in a folder. I want to open a new spreadsheet, run this macro, select the folder with the Workbooks in, and have it take the range selected from the worksheet 'other' from each of the workbooks and generate a 'master' Spreasheet where each tab would be called Workbook1, Workbook2, Workbook3, Workbook4 and the contents would be from the 'other' tab

I found some of Ron de Bruin's code online which I've tried to customise.

Currently this takes a range from the tab specified, puts it into an array and then pastes it all into different columns on one worksheet. Could someone help me change this so that it creates a new worksheet for each original workbook, and names it after that workbook.

Please Login or Register  to view this content.

So I would suggest stepping through your problems piece by piece so you can solve them.
There is value in looking at the entire process at once, BUT I highly suggest you work through each problem to get a solution and THEN start tweaking for efficiency.


So what I have done with things like this in the past is create two macros, one to list all the foldes in a file and another to actually process the data listed from the other macro. These keeps the code more simple for those just starting out with VBA (like myself) and keeps it easier to audit as well.

'WORKBOOKS TO SHEETS
Here's a base macro for collecting data from all files in a specific folder. This version copies the sheet in as a whole.

Workbooks to Sheets - PART 2

The parts of the code that need to be edited are colored to draw your attention.

Yes, let's update the LEFT function to first strip off the extension and then only use the first 29 characters:

Please Login or Register  to view this content.

That should work better. I'll update the code on the site, too.