ALL Excel Files in Folder - Copy & Paste SPECIFIC RANGE values of ALL WBs in SUMMARY WB

[acsishere]

Dear Friends,

I have a SINGLE FOLDER in which 200-300 excel files (xlsm, xlsx, xls) are saved. These are collected from the users after some inputs. I am to CONSOLIDATE/SUMMARIZE the collected data from all workbooks.

Sheet "Main" is protected with password "123".
All other sheets are protected with password "456".

The name of the sheet from where the SPECIFIC RANGE TO BE COPIED is mentioned in cell F6 of sheet called "Main".

The Ranges are to be copied are:
Sheet2 ("AAA"): Range (C105:H120)
Sheet3 ("BBB"): Range (C75:H85)
Sheet4 ("CCC"): Range (C88:H99)
Sheet5 ("DDD"): Range (C60:H70)
Sheet6 ("EEE"): Range (C40:H55)

What to COPY & where to PASTE:

If after the sheet "Main", "Sheet (EEE)" is visible, then the referred range of "EEE sheet" to be copied in the SUMMARY WORKBOOK from which the MACRO IS INITIATED.

If after the sheet "Main", "Sheet (DDD)" is visible, then the referred range of "DDD sheet" to be SUBSEQUENTLY copied in the SUMMARY WORKBOOK from which the MACRO IS INITIATED.

So that, all ranges of VISIBLE workbooks of that particular folder are COPIED and PASTED AS VALUES in sheet1 of workbook called "SUMMARY".

After extracting data from each file, each sheet must be protected with the same password.

In nutshell, What MACRO TO DO?:
1) Macro runs from file "SUMMARY".
2) Select the folder
3) Each workbook - unprotect password of sheets ("Main") & Other "visible sheet also".
4) Pick-up value from cell F6 (this is sheet's name to be extracted) and activate that Sheet.
5) Extract the values from range of SPECIFIED cells
6) Activate SUMMARY workbook.
7) Paste values in Sheet1.
8) Protect all sheets with same password ("Main" with 123; "Other sheets" with 456).
9) Close the opened workbook.
10) Repeat the above processes with other workbooks till the last workbook and SAVE everything in SHEET1 of SUMMARY WORKBOOK.

I have attached a sample file in which the details are again mentioned.

I request your kind help, please.

[MarvinP]

Hi acsishere,

In newer versions of Excel there is a tool called Power Query or Get & Transform (in 2016 Excel) that has what you are looking for. It will pull in multiple files into a single folder into a master workbook.

https://support.office.com/en-us/art...8-6a00041c90e4

If you need to do this many times, I'd consider upgrading your version of Excel to have this new tool.

[acsishere]

Any help, please.....

[acsishere]

Dear Friends,
Request experts help, pl.....

[MarvinP]

Hi acsishere,

Read the section about getting your questions answered quickly at the link below. You need to scroll down a page or two to see that section. Your example didn't have data in it. I suggested a newer version of Excel. Do you need to stay with 2007? Do you want a VBA solution? If you do, what would you do manually to product your desired answer?

https://www.excelforum.com/forum-rul...rum-rules.html

[acsishere]

Dear Sir,
I am using 2007 version and the same is used in almost in all other terminals who are going to use this code.

Therefore, I request a VBA to automate the process. Keeping the complications in view, I change the design of my sheets. The changes are that instead of varied ranges in visible sheets to be copied, I would like to specify that "D101:N110" to be copied and pasted in Summary Workbook

As mentioned in my first post, the code need to:
1) Macro runs from file "SUMMARY".
2) Select the folder
3) Each workbook - unprotect password of sheets ("Main") & Other "visible sheet also".
4) Pick-up value from cell F6 (this is sheet's name to be extracted) and activate that Sheet.
5) Extract the values from range of SPECIFIED cells (in visible sheet, the range always will be D101:N110.
6) Activate SUMMARY workbook.
7) Paste values in Sheet1.
8) Protect all sheets with same password ("Main" with 123; "Other sheets" with 456).
9) Close the opened workbook.
10) Repeat the above processes with other workbooks till the last workbook and SAVE everything in SHEET1 of SUMMARY WORKBOOK.

As I am novice in VBA, please let me know that where & what kind of manual work I can do to support the VB Code. Please help me Sir, so that I will be able to learn.

[Sc0tt1e]

The selct case and copy and paste bits might need a bit of work and not sure about the visible/password unlock without actually playing with the workbook but I'm laying in bed dying from the flu so if it's not 100% correct tough but this should be enough of a skeleton to build upon and I'm sure others can help if you have any issues with the code.

Please Login or Register  to view this content.

[acsishere]

Dear Sc0tt1e,
First of all, I appreciate your time & commitment even if you're not well. I pray Almighty for your speedy recovery.
I tried with the code which gives error at

Please Login or Register  to view this content.

. Unable to understand what error it is and how to resolve.
Any try by any one??

[Sc0tt1e]

what was the error code?