Unprotect workbooks ›—› Encrypting and Decrypting workbook security when opens

Hi All,

I have four different workbooks which are encrypted (protected security) with different passwords. so everytime I have to put the right password for accessing any one of them (Password to Open).

Workbook Kingdom1 has password ›—› a123
Workbook Kingdom2 has password ›—› b123
Workbook Kingdom3 has password ›—› c123
Workbook Kingdom4 has password ›—› d123

I have written some codes in a module to perform actions in the workbook - Kingdom1's Sheet1. Then I want to copy the data from Kingdom1 worksheet to the rest of the three workbook (on the Sheet1).

I would like to run the codes from the 1st workbook - Kingdom1

1) How can I automatically open above encrypted workbooks? so that I don't even have to enter password for accessing the other workbooks.
2) How can I paste the data from Kingdom1 workbook's Sheet1 to the other workbooks Sheet1? (If the data lies from just below to the top header row to the 50th row in the workbook Kingdom1' Sheet1, so that should be copied and pasted in the other workbooks.)
3) After copying and pasting data from one to another workbooks, how can I save & close the rest of the workbook?
4) At first, while running the codes, how can I ensure that the rest of the workbooks are closed? so the macro will not be stucked.

P.S. ›—› All the four workbooks are in different folders(different location in the drive).

I am sure that someone will come up with solution & help me.

In MS Excel 2003: Tools >Options >Security >Password to Open
In MS Excel 2007: On the top left side (on the MS Office icon) >Prepare >Encrypt Document

Thanks in advance!

It would help a great deal to see the macro's taht you are running as they should not affect an open workbook other than the one they are in. The following macro (untested) assumes that all the workbooks (except Kingdom1) are in the same folder on their own.

Please Login or Register  to view this content.

Sorry, i'll have to re think this, it's an old macro rehashed, Filesearch isn't supported anymore after xl2003. it's 4:45am here so it may be a little later when i get up before ican give you another solution.

Thanks for ur reply!
I forgot to mention in the thread - All the four workbooks are in different folders(different location in the drive).

well in that case you need to:
1. give all the locations (drive paths)
2. give the range that should be copied
3. give the destination range
4. state whether the workbook running the code is either none of those workbooks or one of them
5. are any of the sheets in any of the workbooks protected

Plz see the attached, you can see in it: I have made four encrypted workbooks, and the 1st workbook contains data, which is filtered by codes and get pasted in a new worksheet("GetDATA"), And I want to send this data from Kingdom1 workbook to another three workbooks.

Now I hope you can visualize the real situation. Please let me know if u need anything more for the same.

For the solution, I have written all the below codes for required actions, plz look at them:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Please Login or Register  to view this content.

All these codes are working fine for me, and this is what I was looking at, but I'm still getting one serious issues — Before copying and pasting the data from one to rest of the workbooks or running the codes, How can I be ensure that all the other workbooks are closed and unshared? so the job will be done successfully!

I have codes in the workbook 'Kingdom1', and would like to run the codes from here to know about the other workbooks if they are not closed by the users and unshared. Then perform the above codes.

I hope somebody will come up with his/her great idea for the above problem!
I would appreciate if some verify my codes and tries to improve them.

Plz checkout the attached.