In Excel, you can write VBA code to link different Excel workbooks or files so that any change made to a source workbook or file will automatically update the data in the destination workbook or file. Now let's say you have a source workbook called source.xlsx and a destination workbook called destination.xlsx. You want to have automatic change of data in cell A1 in the Sheet1 of the destination workbook after the change to the data in cell A1 in the Sheet1 of the source workbook. To accomplish this task you will follow the steps below.
-First, open the source workbook. Then press Alt+F11 to open the Visual Basic Editor.
-Double-click the Sheet1. On the Object drop box, select Worksheet. And on the Procedure drop box, select Change.
-Paste the following code to the Worksheet change procedure:
Dim des As String
Dim WatchCell As Range
Dim Intersecttion As Range
des = "destination.xlsx"
Set desfile = Workbooks.Open(des)
Set WatchCell = Range("A1")
Set Intersection = Intersect(Target, WatchCell)
If Intersection Is Nothing Then 'do nothing
desfile.Sheets("Sheet1").Range("A1").Formula = "='[source.xlsx]Sheet1'!$A$1"
To test the code, now go to the Sheet1 of the source workbook and enter a value in the cell A1. Then open the destination workbook file to see the change.
Posted by: Lavy | post date: 12-21-2013 | Subject: MS Excel
This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages: