Update between two workbooks.........

[M G Henry]

I am working on a worksheet that relies on data from a log that is
kept on another workbook. The log workbook is updated constantly by a
team of 12 people and no two people can be in the log at the same
time. So it works very nicely for that purpose.

I am builidng in another ' calculation ' workbook a worksheet that
will use the data from the log workbook and the 3 fields from the log
will be used as descriptions from one entry from the log. For ease
of update I would use a second worksheet in the calculation workbook
to hold the entire contents of the original worksheet from the log. as
the one entry could be from any row from the log.

What I would like to happen ideally is that every time the log
workbook is closed, the contents of the log page are updated to the
page I will work from in the calcualtion workbook.

 

[Otto Moehrbach]

I think you want more than this, but this macro does what you say you want.
I chose "Calculation.xls" as the name of the workbook into which you want
the log pasted. I assumed that the Calculation workbook has a sheet named
"Log Sht" and that the log data will be pasted to that sheet. I also
assumed that the Calculation workbook is open. I also assumed that the log
workbook has a sheet named "Log". Make changes to this macro as needed to
go with what you have.

This macro must be placed in the workbook module of the log workbook. This
macro fires when the log workbook is closed. The macro does the following:

Clears all cells in the Calculation workbook, sheet "Log Sht".

Copies all the cells in the Log sheet of the log file.

Pastes into A1 of the "Log Sht" of the Calculation workbook. Post back if
you need more. HTH Otto

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim wbCalc As Workbook

Sheets("Log").Select

Set wbCalc = Workbooks("Calculation.xls")

With wbCalc.Sheets("Log Sht")

.Cells.ClearContents

Cells.Copy .Range("A1")

End With

End Sub

 

[M G Henry]

I think you want more than this, but this macro does what you say you want.
I chose "Calculation.xls" as the name of the workbook into which you want
the log pasted.  I assumed that the Calculation workbook has a sheet named
"Log Sht" and that the log data will be pasted to that sheet.  I also
assumed that the Calculation workbook is open.  I also assumed that thelog
workbook has a sheet named "Log".  Make changes to this macro as neededto
go with what you have.

This macro must be placed in the workbook module of the log workbook.  This
macro fires when the log workbook is closed.  The macro does the following:

Clears all cells in the Calculation workbook, sheet "Log Sht".

Copies all the cells in the Log sheet of the log file.

Pastes into A1 of the "Log Sht" of the Calculation workbook.  Post backif
you need more.  HTH  Otto

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim wbCalc As Workbook

    Sheets("Log").Select

    Set wbCalc = Workbooks("Calculation.xls")

    With wbCalc.Sheets("Log Sht")

        .Cells.ClearContents

        Cells.Copy .Range("A1")

    End With

End Sub







- Show quoted text -

Otto,

Everything is workable except the workbook Calculation is not
necessarily open at the same time as the log workbook.

I was also thinking of a possible conflict.... if someone is in the
workbook Calcuation when the log workbook is closed, would it present
a problem to the fact that the person in the workbook Calculation
would be selecting a line of data when the macro from the log workbook
deletes the log sht worksheet contents in the Calcuation workbook ???

Thanks for your help thus far.....

 

[Otto Moehrbach]

Obviously that would be a problem. If you can't manage the state of the
Calculation file when the log file is closed, then you cannot use the
Before_Close event as the trigger. The only solution I can see, in that
circumstance, is to manually trigger the update where and when you have
control of both files. You would then use the following macro. Note that
only the first line (the macro name) is changed.
HTH Otto
Sub UpdateCalc
Dim wbCalc As Workbook
Sheets("Log").Select
Set wbCalc = Workbooks("Calculation.xls")
With wbCalc.Sheets("Log Sht")
.Cells.ClearContents
Cells.Copy .Range("A1")
End With
End Sub

I think you want more than this, but this macro does what you say you
want.
I chose "Calculation.xls" as the name of the workbook into which you want
the log pasted. I assumed that the Calculation workbook has a sheet named
"Log Sht" and that the log data will be pasted to that sheet. I also
assumed that the Calculation workbook is open. I also assumed that the log
workbook has a sheet named "Log". Make changes to this macro as needed to
go with what you have.

This macro must be placed in the workbook module of the log workbook. This
macro fires when the log workbook is closed. The macro does the following:

Clears all cells in the Calculation workbook, sheet "Log Sht".

Copies all the cells in the Log sheet of the log file.

Pastes into A1 of the "Log Sht" of the Calculation workbook. Post back if
you need more. HTH Otto

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim wbCalc As Workbook

Sheets("Log").Select

Set wbCalc = Workbooks("Calculation.xls")

With wbCalc.Sheets("Log Sht")

.Cells.ClearContents

Cells.Copy .Range("A1")

End With

End Sub

message





- Show quoted text -

Otto,

Everything is workable except the workbook Calculation is not
necessarily open at the same time as the log workbook.

I was also thinking of a possible conflict.... if someone is in the
workbook Calcuation when the log workbook is closed, would it present
a problem to the fact that the person in the workbook Calculation
would be selecting a line of data when the macro from the log workbook
deletes the log sht worksheet contents in the Calcuation workbook ???

Thanks for your help thus far.....