Hi Everyone. My issue is this: In the company I work for we have an Excel file called OrderList. As named, we record all our orders etc. in there e.g. Sheet 1 will be the Orders, Sheet 2 the order figures for the month, Sheet 3 orders not yet confirmed etc. Hope you get the picture. All the sheets are renamed obviously as being Orders, Orders outstanding, Not Yet Confirmed etc. What we want to do is create another copy (Workbook) of that file and merge the two to thus contain the same information. However, the first Workbook should only have 1) Sheet 1 which is all the Orders overall but no other worksheets 2) The second Workbook should contain that same info (Orders) plus the others I mentioned above - Orders not yet Confirmed, Orders Outstanding. (This should not be in the first Workbook) The general idea is that we want to use the first one as a 'master' for overall Orders only. The reason is that the secon Workbook will contain Pivot Tables etc. that we don't wnat in the first Workbook. The other thing is that when we save the changes in the first one, will it be able to have a feature where the second Workbook will automatically be updated? We don't want to make changes to the Orders sheet (Sheet1) in the second Workbook, but want it to be 'updated' automatically by saving the changes in the first Workbook. Hope this is clear. Thanks for your ideas in advance

I'm sure there is something I'm missing, but why isn't it as simple as this? Save OrderList as a 2nd workbook - called something like Complete_OrderList. Delete Sheets 2 and 3 in the original OrderList workbook. In Sheet1 of Complete_OrderList, reference each cell in Sheet1 of OrderList - e.g. A1 contains =[OrderList.xls]Sheet1!A1 A2 contains =[OrderList.xls]Sheet1!A2

OK, I had another idea. My first suggestion will reflect the contents of the cells from the Orders sheet to the 2nd book (Complete_OrderList) but it won't reflect any format changes, etc. The following code will delete the existing Orders sheet from Complete_OrderList and copy the Orders sheet from the OrderList workbook every time Complete_OrderList is opened. That way it will be an exact duplicate of the Orders sheet. However, it will not reflect changes that are made while both workbooks are open. If you need the Complete_OrderList workbook to reflect changes made to OrderList both while they are open (i.e. live) *and* upon opening Complete_OrderList, then things get a little more complicated. Let me know. If you paste this code into the This Workbook module of Complete_OrderList, it will run each time Complete_OrderList is opened. Private Sub Workbook_Open() 'Turn off Alerts and ScreenUpdating so update happens in the background Application.ScreenUpdating = False Application.DisplayAlerts = False 'Open the OrderList workbook Workbooks.Open Filename:="H:\dave marulli\Excel\OrderList.xls" 'Delete Orders sheet from Complete_OrderList.xls Workbooks("Complete_OrderList.xls").Sheets("Orders").Delete 'Activate and Copy the Orders sheet from OrderList.xls Workbooks("OrderList.xls").Activate Workbooks("OrderList.xls").Sheets("Orders").Copy Before:=Workbooks("Complete_OrderList.xls").Sheets(1) 'Close the OrderList workbook Workbooks("OrderList.xls").Close End Sub

Not an answer, but a question. Why do so many insist on using Excel when a proper program would be so much easier? This is clearly an Order Entry / Inventory Control application. You could have thousands of items with history available for any specified period.

Hi DerbyDad03 Thanks very muh for your response and advice. I will give this a shot as soon as. ust need to do something else first and then I'll post back. Thanks

Hi DerbyDad03 Thanks for your post. I'm sorry to say but I have no idea what you explained to me. How far I've came so far: I shared the original OrderList as a Workbook I then made a copy (for testing only) of the original OrderList and rnamed it OrderList 2. When I make changes to the original OrderList I save and close it. I then open OrderList 2 and go to Tools>Compare and Merg Workbooks and then merge Orderlist 2 with he original OrderList. The problrm with this is that it merges all the worksheets. I only want the first sheet (named Main Orders) to be merged into OrderList 2. How is that possible? Ideally I would like this merge to happen automatically without me having to go into OrderList 2 and going to Tools>Compare and Merg Workbooks. I also want to delete all the other sheets from the original OrderList but I can't seeing that it is a shared Workbook. Any ideas? Thanks in advance and sorry for not understanding what you meant in the first post.

I believe I gave you 2 ideas: One is to use formula's in the first sheet of OrderList 2 that points to the Orders sheet in the first workbook. A1 on Sheet1 of OrderList 2 contains the formula =[OrderList.xls]Sheet1!A1 A2 on Sheet1 of OrderList 2 contains the formula =[OrderList.xls]Sheet1!A2 Whatever you put in A1 of Sheet1 on OrderList will also appear in A1 of Sheet1 on OrderList 2. The cells are "linked" so that changes to the first one are reflected in the second. The other option was to use VBA code to automatically delete the Orders sheet in OrderList 2 and replace it with the Orders sheet from Orderlist every time OrderList 2 is opened. That way you will get an exact copy of the Orders sheet from OrderList including formats, formulae, pictures, backgrounds, etc.