VBA copy only green tabs from closed WB to a new WB

[gubertu]

Hi all,

I hope you can help me with the following code I need.

I have 10 different WBs in a folder, let´s say C:\Users\Downloads.

I would like to have a code that goes into every closed WB in the folder and copy only the green colour sheets into a new WB.

For example:

WB 1 name: Debt
Number of sheets: 5 sheets in the WB, only one sheet is green coloured, called "Summary".

WB 2 name: Assets
Number of sheets: 3 sheets in the WB, only one sheet is green coloured, called "Assets".

Final result:

I would like to create a new WB, with only the two sheets green colour, in this case, "Summary" and "Assets".

Thanks in advance for your help!

 

[gubertu]

Hello,

any help? Maybe I didn´t explained myself well.

Thanks

 

[Marc L]

Hello,​

​

difficult to help as I can't guess the color value of your green tab, should be easier with an attachment …​

 

[gubertu]

Hello,

I think it is very complicated to extract a sheet by color. Is it possible to tell the macro to go to the WB called Debt and extract Summary and go to the WB called Assets and extract "Assets" and put them together?

Thanks in advance!

For example:

WB 1 name: Debt
Number of sheets: 5 sheets in the WB, I want to extract the sheet called "Summary".

WB 2 name: Assets
Number of sheets: 3 sheets in the WB, I want to extract the sheet called "Assets".

Final result:

I would like to create a new WB, with only two sheets. In this case, "Summary" and "Assets".

 

[Marc L]

Not difficult, if only both have the same color value, easy to check with an attachment …​

​

Can be achieved according to worksheet name rather than color value​

but without any attachment I can't guess what is the full filename - with the file extension - of each workbook …​

Are they opened ?​

Where the VBA procedure should be located ?​

 

[gubertu]

Hello,

I don´t know how can I upload my file; however, I will ask your questions:ç

1) File extension *.xlsx.
2) I only need to extract from book "Debt.xlsx" the tab "Summary" and from book "Assets.xlsx" the tab "Assets". This tabs need to be pasted as values and in the same format in a new book.
3) All the books are closed.

Hope I could help you.

Thanks in advance

 

[Marc L]

I don´t know how can I upload my file

Via a link on each workbook on a files host website like Dropbox …​

​

Anyway,​

3) All the books are closed.

So they need to be opened under the VBA procedure but as your forgot to indicate their path, as coding can't be guessing …​

​

 

[gubertu]

Via a link on each workbook on a files host website like Dropbox …​

​

Anyway,​

So they need to be opened under the VBA procedure but as your forgot to indicate their path, as coding can't be guessing …​

​

 

[gubertu]

Hello,

I have uploaded the files here: VBA COPY FROM CLOSED WB

The files are in the following patch: C:\Users\Downloads.

Please confirm if that is enough.

Thanks in advance

 

[Marc L]

​

I've just read again your initial post (sorry I didn't remember the path was there) and now I'm in a doubt :​

your attachment contains only two workbooks but your initial post stated about ten workbooks so what is the purpose ?​

To open only two specifics workbooks of ten (aka Assets & Debt) or to open all workbooks for specific worksheets names ?​

If it's the second case then well described the expected result 'cause what if several workbooks contains the same worksheets names ?​