Combine up to 3 worksheets from up to 3 workbooks into new workbook if Sheet name is same

Hi everyone,

I have 3 workbooks with multiple worksheets. Workbook1, Workbook2, Workbook3

All three workbooks can be open at the same time

I have a list of worksheet names found in Master_Workbook column A1

What I'm trying to do is have a macro which will loop through the list of worksheet names in Master_Workbook column A1, and look through all three workbooks. If at least two workbooks have a worksheet with the same name, it will merge to the two (or three) into a new workbook with the same name.

I've been doing some research on this but I haven't been able to find anything quite like this.

Any ideas?

Thanks!!

Hi Bernie,

Thank you very much for your code, as well as your post on the developers forum where I also inquired for help! I'm trying to make some small adjustments to your code to fit the actual workbook. I have updated the master sheet name. The list of worksheet names is now found in cell P3 and down rather than A1. I ran your code with the adjustments I was able to make, and there were no errors, however I found that the new workbooks only contained one worksheet rather than up to 3 and that column A:A seemed to list the workbook name the data came from. Is the data being copied into a single sheet? If so, how can we adjust so that each sheet appears separately in the new workbook? I apologies if my description was the cause of this confusion.

Hi Bernie,

Thank you for removing the references. I'm trying to understand your code for my own knowledge as this type of coding is quite a bit beyond my current skill level. Do I understand correctly that the following section of code copies the worksheets into the new workbook as new worksheets rather than copying the data from the 3 worksheets into a single worksheet in the new workbook?

Please Login or Register  to view this content.

Hi Bernie,

The code works perfectly, thank you! One last thing. Master_Workbook has a sheet called Sum. I need to copy the Sum sheet to each of the output workbooks, so it would be the 3 worksheets plus a copy of the Sum sheet in front of them. I'm trying to add it into your code without much success. Can you assist?

Hi Bernie,

You've been awesome so far, thanks very much for your help. Two questions:

1. Worksheets("Sum") in the newly created workbook (with the other 3 worksheets we added).range("D15") needs to equal worksheet(2) (the 2nd worksheet in the new workbook).Range("D44"). How can I reference this?
2. Similar to the top, Worksheets("Sum").Range("D16") needs to equal Workbooks("Master_Workbook.xlsx").Worksheets("Supplimentary").Range("B12"). How can I reference this?

Thanks!!

Hi Bernie,

Thanks. I actually used those same codes you entered but I'm not getting the values copied over. range("D44").value has a formula in it. Does that make a difference?

Ah, I figured it out. Just need to put the .value = .value lines lower in the code. I put them after I delete a couple of unused worksheets.

Hi Bernie,

For

Please Login or Register  to view this content.

Instead of specifically identifying cell D44 in Worksheets(2), how can I adjust this to select the last cell in row 44 with data. Not all cells in this row contain data, so its the absolutely last cell in this row that contains it. The data in this row won't go beyond column Z.

Thanks!!

Ah, figured it out. Admittedly a backwards way of doing it:

Please Login or Register  to view this content.

Hi Bernie,

Now I definitely need your assistant. For pulling the a value from the Master_Workbook workbook

Please Login or Register  to view this content.

What I'd like to do is copy an entire range. So instead of icking B12 specifically, the line would look through column A of worksheets("Supplementary") find the value which is the same as the rngC.value, then copy the values adjacent to it in column B and 5 rows down. So if it happen to find rngC.value in say A23, then it would copy B23-B28 to worksheets("Sum").range("D16") and down.

Any ideas?

Thanks!!

Any ideas?

Hi Bernie,

The code seems to be copying the data in Supplementary across to the right instead of down 5 rows?