auto update

[kendel]

Hello,
We're using Excel 2007 with XP. Two workbooks are used for production scheduling. WB 1, the master, is used for bringing in orders and sorting to 6 workgroups. WB 2 is used to schedule the work. The workbooks cannot be combined. WB 2 is a copy of WB 1 with the addition of scheduling algorithims for each workgroup, an additional 7 sheets. Presently these unique worksheets from Wb 2 are cut and pasted into a copy of WB 1 daily to recreate/update it. I would like WB 2 to update itself automatically eliminating the cut and paste and potential for errors and missing orders.

Thanks for any help on this!

 

[Domski]

The workbooks cannot be combined.

Why is this? If they are in theory able to update themselves then they must be in the same location so surely a single data source would be a far better idea than trying to maintain two versions of the truth which is likely to be prone to possible errors.

Dom

 

[kendel]

Thank you for the response! I figured I'd just lay out the situation as clearly as I could without too much info. The books cannot be combined because they are maintained and used by two people at the same time, both of which have other duties that preclude one person doing it all. WB 1 is an overview/sorting of several weeks of production generated by the merchandising dept, and WB 2 is the workgroups' daily schedules with detailed instructions and metrics generated by the production supervisor.
Is what I'm asking even possible?

 

[Domski]

Possible I guess but pretty complicated.

My thought would be to have a live data workbook that is updated constantly with an analysis workbook that pulls a copy of the data as required.

Dom

 

[kendel]

Possible I guess but pretty complicated.

My thought would be to have a live data workbook that is updated constantly with an analysis workbook that pulls a copy of the data as required.

Dom

Thank you so much for taking the time to reply.
'Pulls a copy of the data' = auto-update?
How exactly does an analysis, or any workbook, pull a copy of the data as required?
Thank you again for your interest!

 

[Domski]

You could use a Workbook Open event in your analysis workbook that each time it is opened pulls the data from the workbook that is being updated. Something like this:

Private Sub Workbook_Open()
Dim wbSourceBook As Workbook
    
Set wbSourceBook = Workbooks.Open(Filename:="H:\DataBook.xls", ReadOnly:=True)
With wbSourceBook
    .Sheets("Sheet1").Cells.Copy ThisWorkbook.Sheets("Sheet1").Range("A1")
    .Close False
    
End With
End Sub

You might need to add other code in to update chart ranges etc depending on how the analysis is carried out.

Dom

 

[kendel]

Thank you very much for your help with this. I will post the Macro that I developed and perhaps you can give me advice as to how to do it better.