Hi,
First, I am using excel 97...
I have about 20 different departments that are goign to have customized workbooks (only 1 sheet) but they will be entering the same type of data in each. Each dept cannot see the others work, so they have to be in different workbooks. But now, there is a top manager would like to look at them w/out opening all 20 workbooks what is the easiest way to do this?? Keep in mind these dept workbooks are updated often, including adding rows, deleting rows etc... I am thinking I'd like a master workbook w/ 20 sheets, 1 for each dept, and have them be able to update to be exactly what's in the dept book.. Is this even remotely possible, or am I thinking way to hard and there is an even easier way to do this?? Thanks guys!
cloning a sheet, in a new workbook???
-
-
-
I think we need to know where the Workbooks are stored i.e are they on a network.
-
I can do whatever is easiest.. I do have the option to store them on the network, I guess w/ updating that would be the only option.. So yes, we can say they'll be on the network.. Thanks!
-
Any ideas?? The only thing I can think of so far is to have a macro that will open the managers workbook, delete all sheets, then opens each and every workbook and copies the sheet to the main workbook?? Seems really inefficient and prone to errors???
-
I believe you have come up with the best solution. Since each of the 20 sheets will have data added to them in a variety of ways, it doesn't seem feasible to have links in the master workbook to each of the sheets. If you put the process of opening and copying the sheets as you describe into a macro, turning screenupdationg to false and having the calulations set to manual until after all of the sheets have been transfered, I think you wil' find that it runs pretty quickly. Have the macro open the 20 workbooks one at a time in read only mode. Remember that the master workbook has to have one sheet in it, so don't delete all 20 old sheets until you have copied at least one of the new sheets.
-
-
Bear in mind that copying a sheet limits the cell contents to 255 characters (don't know that this will be an issue for you, but it will truncate longer cell contents). This can be worked around by selecting the used cells and pasting them into a blank sheet in the manager's workbook.
If your macro cycles through the 20 files in the same order, couldn't you erase the existing data in the manager's book, then copy the new data without deleting the sheets?
-
Thanks guys..
I suppose I could just copy/paste the entire contents of the sheet also Brandtrock.. I will probably try both and see which I like best..Derk,, or anyone for that matter.. what do I put in the code to tell it to open in read-only?? I haven't done that before, or ever thought of it for that matter, but sounds like it is a good thing to do....
Derk,, thanks for the tip on not deleting all the sheets!
-
To open in readonly mode:
Application.Workbooks("path\whatever.xls").Open ReadOnly:=True -
Thanks a lot Derk!!! Appreciate it!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!