Match Flies

[Leo1452]

Hi everybody,

I need your help. I have 2 Excel files. They are exactly the same. Eac
one has 5 worksheets.

I want to match them to each other. If I change some thing in de firs
file, work sheet 2 cel A2 for example. It wil change it automatical
also in the another file.

Ik hoop I have my quesion clear asked en I hoop to get an answear a
soon as possiable.

Kaind regards,

Leo1452 :confused

 

[ManualMan]

Hi Leo,

This one works when you apply changes in a single sheet on one ore
multiple cells:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

mySheet = Target.Worksheet.Index

Workbooks.Open Filename:="[Path] & bestand1.xls"

For myRow = Target.Row To (Target.Rows.Count + Target.Row - 1)
For myCol = Target.Column To (Target.Columns.Count + Target.Column -
1)
Workbooks("bestand1.xls").Worksheets(mySheet).Cells(myRow,
myCol).Value =
Workbooks("bestand2.xls").Worksheets(mySheet).Cells(myRow, myCol).Value
Next myCol
Next myRow

Workbooks("bestand1.xls").Save
Workbooks("bestand1.xls").Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Regards,
ManualMan
http://www.gamesXL.tk

PS. Aangezien jij volgens mij nederlands kan lezen heb ik hier en daar
wat nederlandse woorden laten staan.

 

[Tom Ogilvy]

Another approach if only one workbook is open at any given time:

Assumes names like mybook1.xls and mybook2.xls. Adjust as appropriate.


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

In instr(Thisworkbook.Fullname,1) then
Application.SaveCopyAs Filename:= _
Application.Substitute(thisworkbook.Fullname,1,2)
Else
Application.SaveCopyAs Filename:= _
Application.Substitute(thisworkbook.Fullname,2,1)
End if

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

For information on Events:

http://www.cpearson.com/excel/event.htm
(Chip Pearson's site)

You may want to add similar code to the before save event.

 

[ManualMan]

Presumably the first word in the quoted line underneath

In instr(Thisworkbook.Fullname,1) then

should be "If" instead of "In" to make the sub work real smooth
Regards,
ManualMan
http://www.gamesXL.tk