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

Barry Katcher · Feb 26th, 2002, 01:10 PM

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.

gplhl · Feb 26th, 2002, 01:24 PM

Quote:

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

Barry Katcher · Feb 26th, 2002, 01:39 PM

Thanks, Gary

Barry Katcher · Feb 26th, 2002, 02:35 PM

Quote:

On 2002-02-26 12:39, Barry Katcher wrote:
Thanks, Gary

simonf · Jun 26th, 2002, 04:15 PM

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.

Barry Katcher · Jun 27th, 2002, 06:28 AM

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.

simonf · Jun 27th, 2002, 11:50 AM

Hi, actually just the column headings are the same. The data are different.

Barry Katcher · Jun 27th, 2002, 01:39 PM

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.

Jun 27th, 2002, 06:28 AM	#6
Barry Katcher Board Regular Join Date: Feb 2002 Location: Dog Beach, Florida. Yeaahh! Posts: 4,038	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. __________________ Barry- Photo Restoration/Enhancement http://www.smiledogproductions.com click below for detour

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 26th, 2002, 01:10 PM	#1
Barry Katcher Board Regular Join Date: Feb 2002 Location: Dog Beach, Florida. Yeaahh! Posts: 4,038	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.

Feb 26th, 2002, 01:39 PM	#3
Barry Katcher Board Regular Join Date: Feb 2002 Location: Dog Beach, Florida. Yeaahh! Posts: 4,038	Thanks, Gary

Jun 26th, 2002, 04:15 PM	#5
simonf Board Regular Join Date: Jun 2002 Posts: 207	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.

Jun 27th, 2002, 11:50 AM	#7
simonf Board Regular Join Date: Jun 2002 Posts: 207	Hi, actually just the column headings are the same. The data are different.

Jun 27th, 2002, 01:39 PM	#8
Barry Katcher Board Regular Join Date: Feb 2002 Location: Dog Beach, Florida. Yeaahh! Posts: 4,038	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.