Open, copy and paste worksheets to another workbook

[anthony_91]

Hello,

Recently, I've been trying to link worksheets from other workbooks to a consolidated workbook. I'm still a beginner at VBA, so this was a little difficult.

Instead, I'd like to open up workbooks, copy selected sheets, move to a consolidated workbook and then close that workbook. I have to do this for about 100 workbooks, so I'm not too sure how quickly I can do this.

Is there some way of doing this efficiently.

Thanks

[HaHoBe]

Hi, anthony_91,

maybe have a look at Merge data from all workbooks in a folder or WBs to Sheets for a start.

Ciao,
Holger

[anthony_91]

Hi, anthony_91,

maybe have a look at Merge data from all workbooks in a folder or WBs to Sheets for a start.

Ciao,
Holger

Thank you for your reply. I've looked at Part 4 of thie WBs to Sheets. I'd like to modify this code to make it more flexible. I'd like to have the ability to choose the path where I pull workbooks from. I'm assuming I have to modify this code in some way.

Please Login or Register  to view this content.

More importantly, I'd like that ability to choose which worksheets to copy by name and into which sheets to paste it into. So for example, if I have a directory on my "C:\Workbooks" and inside that folder I have Workbook_A.xlsm, Workbook_B.xlsm, Workbook_C.xlsm and I want to copy SheetBBB from Workbook_B.xlsm into my consolidated workbook, how can I do that.

[HaHoBe]

Hi, anthony_91,

regarding the path: ThisWorkbook.Path is the path for the workbook with the code. If you want a different directory please make sure that the last character on a windows system is a backslash.

Regarding the integration of special sheets from certain workbooks and their destination in the target workbook: should that be the only way to go (not look at any other files in the directory) or is it like take all sheets from all workbooks not specified in the given list?. You could add the workbook name to open, the sheetname to copy and in a thrid column the destination and run a loop on that in an extra worksheet. If all other workbooks are to copied completely you could run a CountIf on the workbook names and decide what to do: 1 says have special orders and 0 just copy everything.

Ciao,
Holger

[anthony_91]

Hi, anthony_91,

regarding the path: ThisWorkbook.Path is the path for the workbook with the code. If you want a different directory please make sure that the last character on a windows system is a backslash.

Regarding the integration of special sheets from certain workbooks and their destination in the target workbook: should that be the only way to go (not look at any other files in the directory) or is it like take all sheets from all workbooks not specified in the given list?. You could add the workbook name to open, the sheetname to copy and in a thrid column the destination and run a loop on that in an extra worksheet. If all other workbooks are to copied completely you could run a CountIf on the workbook names and decide what to do: 1 says have special orders and 0 just copy everything.

Ciao,
Holger

Hi Holger,

Thank you for replying. Do I put the path in quotations, e.g.

Please Login or Register  to view this content.

So, to go into more detail as to what I'm trying to do. Say I have 5 workbooks in C:\Workbooks

Workbook_A.xlsm
Workbook_B.xlsm
Workbook_C.xlsm
Workbook_D.xlsm
Workbook_E.xlsm

Each of those workbooks has 10 sheets. I only want to copy 2 of those worksheets with a particular sheet name into my consolidated workbook.

How could I do this with VBA?

Thanks

[HaHoBe]

Hi, anthony_91,

if there is only one person anyswering to the thread you donīt need to quote the whole post.

You would need to alter the sheetnames to suit but the code passage may look like this:

Please Login or Register  to view this content.

Ciao,
Holger

[oeldere]

@antony_91

To me this looks as the same question; if so this is an duplicated post, wich is against the forum rules.

http://www.excelforum.com/excel-prog...t-opening.html

[anthony_91]

This code works. Thank you Holger for your help

Please Login or Register  to view this content.

[anthony_91]

@antony_91

To me this looks as the same question; if so this is an duplicated post, wich is against the forum rules.

http://www.excelforum.com/excel-prog...t-opening.html

Hi, no it's a different post because I was asking how to link cells without opening the workbooks. I'm now asking how to copy and paste into a consolidated workbook.

Hope that clarifies that.

[oeldere]

@anthony_91

Hi, yes this clarifies that.

Thanks for the reply.