Copy sheets from two workbooks to another workbook

Hi All,

I tried searching forums to do the below function, unfortunately could not find it.
I am struggling with the code, however it does almost what I want but have one issue.

Problem Definition:
I have two workbooks:
1. Book1 and sheets in this workbook are as follows TC1, TC2,TC3,TC4
2. Book2 and sheets in this workbook are as follows AC1, AC2, AC3, AC4

When I run a macro, specific sheets named in the code from Book1 and Book2 will be copied in Book3 with same sheet names.
Once copied, Book1 and Book2 and Book3 should close and be placed in the same folder.

I am trying to run the below code, forgive me for the logic, however am stuck.
The below code copies and pastes on one sheet only and does not add a new sheet.

I am fairly ok in macros but get stuck in looping. The below code was developed by my colleague and I am posting here.

Kindly request you all to help me out on the below.

Best Regards,
Sri

Please Login or Register  to view this content.

The code doesn't seem to match the description.

For one thing it involves Book1, Book2, Book4 and Book5.

Also, what's happening here?

Please Login or Register  to view this content.

You appear to be looking for a workbook called Book1.xlsx, if it doesn't exist you are creating a new workbook and trying to name it Book4.xlsx.

When creating a new workbook you can't name it.

Also, after creating the new workbook you try and open it, the workbook hasn't been saved yet.

Which worksheets do you want to copy from which workbooks?

Which workbooks are open when you run the code?

Do you want to copy the worksheets or the data from the worksheets?

Hi Norie,

Thank you so much for your help and quick response.


Below are the comments:

Which worksheets do you want to copy from which workbooks?
I would like to copy specific sheets from workbooks, I am ok if we can provide the source sheet name in the macro
This way for different scenario, I will change the sheet name to copy in the macro it self for both InputBook1 and InputBook2

For example:
1. InputBook1 has three sheets "attributes_s", "ag_id", "tbr_detail", "X", "Y" and "Z"
2. InputBook2 has three sheets "arrangement_s", "X" and "Y"

Objective:
1. Open InputBooks, copy specified sheets from these two books and paste it in the OutputBook1 i.e.
a) Copy "attributes_s", "ag_id", "tbr_detail" from InputBook1
b) Copy "arrangement_s" from InputBook2
c) Paste the above sheets in OutputBook1





Which workbooks are open when you run the code?
Both the Input books should be closed. However both input books will be in same folder


Do you want to copy the worksheets or the data from the worksheets?
I would like to copy the sheet itself along with its name.

Thank you once again for your support this would mean a lot to me and save like huge amount of hours.

Best Regards,
Srikanth

Perhaps this will give a start.

Please Login or Register  to view this content.

Hi Norie,

Just able to really appreciate, the code I shared earlier was so many lines and we were really scratching our brains.
The code you shared is so simple and organized and logic is implemented perfectly.

However, once compiled it is showing an error message at this line:

Run-time error '9':
Subscript out of range

Please Login or Register  to view this content.

Best Regards,
Sri

Check the sheets in your workbooks, the second input workbook only has 1 sheet.

Hi Norie,

Thank you for the support...
Please let me know if there is an easy workaround for the above.

Best Regards,
Sri

Sri

Might be an idea to start a new thread for this new question.