Home |
Search |
Today's Posts |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi,
Thanks for the feedback! Sounds like things are working better. I'm not sure if the 1000's of formulas problem is still an issue, but, and I've never tried this, you might be able to put the Replace All command into code and use the Application.DisplayAlerts = False command to suppress them. You could record the Replace command in a separate workbook where you don't have to deal with the popup. Then you could modify it to do what you need and add the line mentioned above before the replace command. Again, I've never tested this with the replace command so I don't know the results, its just a thought. -- Thanks, Shane Devenshire "Eric" wrote: I was able to correct the original problem by following the two recommendations from shg and ShaneDevenshire. With the master sheet closed I Saved As #1 to each of the other component spreadhseets, overwriting instead of deleting, and the filenames on the master sheet did not change. Before doing that I did the same thing, but with the master sheet open and the filenames did all change to the last one overwritten. So the key to the problem is to close the master sheet before deleting or overwriting the component sheets referenced in the master. Eric "Eric" wrote: OK thanks to both of you. I was so shocked that Excel changed the workbook names I just wanted to lock them in place as absolutes, which I would do if I knew how. All your suggestions make sense, and I am sure I can work through that. Closing the master and saving over the component spreadsheets instead of deleting them I think will be the best solution. Interestingly, I just found that once I recreated all the ocmponent files the workbook references all changed back to what they were supposed to be. This is good, but I would love to take control away from Excel in this case. I have a second problem when changing the formulas in the master that reference the separate workbooks. With 13 columns and 100 rows that is 1300 formulas, with 100 to each column. I have to change the references if I make a change to the component format, and alter the location of the cells I am referencing on the separate workbooks. What I find is that when I change a formula with a separate workbook reference I am prompted for file location. I am not changing the location, and I can cancel that dialog by hitting the cancel button or just hitting escape, however when I am changing 100s of formulas I want to use Replace All, and that means hitting Esc for every cell being changed during what should be a one touch procedure. Further, there are times when it doesn't do this, and it seems arbitrary to me. It did this when the component spreadsheets were deleted, but recreating them all didn't ichange the result. I just went back again to try it and now I am not getting the prompt. Again, I seek control over this process, which serves no purpose in the first place since I am not posting a new location. The arbitrariness of the problem suggests there is a bug here, but I am still wondering whether there is a way to prevent the prompt. Thanks again for your help. Eric "ShaneDevenshire" wrote: Hi, First thing I would try is 1. close the Master leaving the revised Worksheet1 open 2. Choose File, Save As and click on one of the files you want to replace. 3. Click Save and respond Yes to replacing the old file. 4. Repeat this for each of the 12 files. Don't delete the old files first, and do this with the Master closed. A second approach if this doesn't work: Why not take a different approach, instead of deleting each workbook. Close the master and open one of the 12 workbooks you intended to delete. Display is workbook and the modified workbook1 side by side. Windows, Arrange, Tile. Then drag the new sheets from the Workbook1 file while holding down the Ctrl key and drop them in the Workbook2 file. Ctrl Drag is a copy command. Now delete the the old sheets and rename the new sheets. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "Eric" wrote: I have a master Worksheet which collects data from workbooks I distribute to others. When I get them back filled in, I put them in the same folder with the master (totals) spreadsheet, and when I open the master spreadsheet it picks up whatever data is currently in the folder. When I get all the spreadsheets back the master is complete, and I can issue a report on the collective data. The master spreadsheet is set up very simply with a column for each spreadsheet, and the same number of rows as in the component spreadsheets (over 100 rows), which are all identical except for the number in their file name (1 - 13). Each cell in a given column references a cell in the respective spreadsheet for that column with the [workbook name]worksheet name!cell reference. That all works fine. I have two separate problems: I am still finalizing the design of the master spreadhsheet and the component spreadhseets. I make changes only to the master sheet and the first component sheet (#1). And only after changes are complete do I create the other 12 component sheets by doing a save as from the #1 sheet. The first problem occurs when I make further changes to the #1 sheet and the master and then delete the other 12 component sheets only to re-create them when I have finished the changes. What I find is that, while each cell in each row in a given column contains a reference to the spreadsheet for that column (1 - 13), after I have deleted all the component spreadhsheets, the workbook name in every cell in every column of the master spreadsheet has automatically changed to the last worksheet I deleted, and I have to change them all back. Profuse apologies for this incredibly long description of the question, but it seemed necessary. I will stop here and not get into the associated issues before there has been discussion on the first. Thanks, if you have read through this entire question. Eric |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Separating worksheets into separate workbooks | Excel Discussion (Misc queries) | |||
how do I get separate workbooks in separate windows | Excel Discussion (Misc queries) | |||
Move worksheets into separate workbooks | New Users to Excel | |||
Save 2 separate data imports in separate worksheets on the same ex | Excel Worksheet Functions | |||
Combine Workbooks as separate worksheets | Excel Discussion (Misc queries) |