Please help, for the 3rd time - I'm drowning!

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
I posted this twice on the old board and didn't get any response. I really need help with this. Thanx in advance.

I have a summary workbook that is linked to twenty departmental budget workbooks It combines all their info into a company-wide consolidation. All the individual workbooks are password protected with different passwords

The problem is that every time I open the summary w/b, I have to manually input all twenty passwords, one after the other. If I just click "o.k." or "cancel" in the password boxes, the workbook opens, but any change in data in the departmental w/b's is not refreshed on the summary w/b.

I tried writing a macro to open the w/b and input the passwords, but that part of the macro pertaining to passwords was not recorded.

Can anyone help with an opening macro? Thanx in advance.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
On 2002-02-26 12:10, Barry Katcher wrote:
I posted this twice on the old board and didn't get any response. I really need help with this. Thanx in advance.

I have a summary workbook that is linked to twenty departmental budget workbooks It combines all their info into a company-wide consolidation. All the individual workbooks are password protected with different passwords

The problem is that every time I open the summary w/b, I have to manually input all twenty passwords, one after the other. If I just click "o.k." or "cancel" in the password boxes, the workbook opens, but any change in data in the departmental w/b's is not refreshed on the summary w/b.

I tried writing a macro to open the w/b and input the passwords, but that part of the macro pertaining to passwords was not recorded.

Can anyone help with an opening macro? Thanx in advance.

Try this:

Sub OpenPassword()
' Password protect the module you put this code in.
Workbooks.Open Filename:="C:file1.xls", Password:="mypassword1"
Workbooks.Open Filename:="C:file2.xls", Password:="mypassword2"
' And so on

End Sub


Word of advice though, password protect the project you put this into otherwise other people could look at the code and get the passwords.

Regards,

Gary Hewitt-Long
 
Upvote 0
Hi, Barry. I also need to do the same kind of consolidation but the different workbooks are not protected. How did you performed the consolidation? Basically the columns on each workbook are: dept, project, module, cost catagory, Q1, Q2, Q3, Q4, Total. Each workbook is a different department. They are all identical in their layouts. Do I need to click on each cell in the summary workbook to set the Data/Consolidation and map to each of the corresponding cell of the various workbooks? Thanks much.
 
Upvote 0
Simonf
Assuming, as you say, all the spreadsheets, including the consolidation sheet, are in the exact same format (same # of rows & columns and same data in each corresponding cell), then you just have to enter one long formula, in the consolidation sheet, in the first cell that contains data. Assume that to be cell A1.

=[Book1]Sheet1!$A$1+[Book2]Sheet1!$A$1+etc., etc., etc.

You can enter this manually in the cell or you can open the workbooks and point to the cell in each.

After you have this one cell, just copy down and then to the right.
 
Upvote 0
Sorry, I was unclear. When I said "the same data..." what I meant was "the same type of data...". If, for example, cell F20 in worksheet 1 (or workbook 1) has column heading of "Annual Budget", and row heading of "Supplies", do these headings apply to all of your worksheets (books) for each of their cells F20? If so, the sheets (books) are identical and the formula can be used. Of course, the actual numerical data for each sheet would be different.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top