LinkBack URL
About LinkBacks
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.
Register To Reply
Bookmarks