Copying data to separate workbook 1

[Chattin]

Rather than having a formula in every single cell is there any way of programmatically copying data from one workbook to the equivalent cell in another workbook. Also when new data is added to the first workbook I'd like that to be copied over also. Basically I want to have two copies of the data in separate workbooks without having to do a copy/paste in Excel or copy the workbook in Windows Explorer. I'm assuming an event handler of some sort but I'm not quite sure how to continue:

Private Sub Worksheet_Change(ByVal Target As Range)

(some sort of activate second workbook and copy data code here)

End Sub

Hope that makes sense....

Cheers !

Chris;-)

 

[dwilson01]

You are correct in your thoughts, however, the change event may not be the best place to put your code. You may want to put it in

Workbook_BeforeClose(Cancel As Boolean)

. This will allow for multiple changes before running any code and interupting the user.

Is the copy workbook always going to be the same? If so, you can hard code the workbook open in the event. If not you could use the xlDialogOpen and have the user open the file.

If you need assistance writting the code, let me know.

dwilson01

 

[Chattin]

The first workbook will be purely a reference database and have no calculations performed but the second will have the data plus various calculations and may have additional worksheets. The second workbook will always be the same file though.

If you could point me in the right direction for the code I'd be most grateful

Ta!

Chris;-)

 

[dwilson01]

I'm in training today but this should get you started.

Private Sub Worksheet_Change(ByVal Target As Range)

'May want to check to see if second workbook is open here
For Each wb In Application.Workbooks
    If wb.Name = "Your Workbook Name" Then GoTo exitloop
Next
Workbook.Open "c:\Your WorkBook Name"
exitloop:
'Set Rng1 to the column that should be used for additions to workbook 2
Rng1 = Workbook(1).Sheets(1).Columns(1)

'Check to see if change occurred in specific column
Set isect = Application.Intersect(Range("rg1"), Range(Target.Address))
If isect Then
    'Add new value to workbook2
    Workbook(2).Sheets(1).Range(Target.Address) = Workbook(1).Sheets(1).Range(Target.Address)
End If

End Sub