Auto Update Cell Value

[tim220225]

Not very good with VBA code here and need some help please. I have two workbooks within the same folder and typically only have one workbook open. I have multiple years of data in the closed workbook. When I am in the open workbook I can call for the data of any year by selecting the year via a drop-down box in a specific cell which is "B1". I need to have the year that shows in the open workbook automatically update the year cell which is "B1" in the closed workbook whenever it is changed in the open work-book. I assume that the code needs to open/update/close the workbook which needs to be updated "behind the scenes".

This works well for me when both files are open but I need to make this work when only one file is open. Can someone help me with a simple code example please?

Tim

 

[DanteAmor]

Put the following code in the events of your sheet 1 of your open workbook.
I guess the sheet is first in both books.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B1")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If Target.Value = "" Then Exit Sub
        '
        ruta = ThisWorkbook.Path & "\"
        arch = "other workbook.xlsx"     'name of the closed book
        '
        Application.ScreenUpdating = False
        Set l2 = Workbooks.Open(ruta & arch)
        l2.Sheets(1).Range("B1").Value = Target.Value
        l2.Save
        l2.Close
    End If
End Sub

Let me know any questions.

 

[tim220225]

Dante,

Thank you for the reply. I inserted your code in the worksheet of the opened workbook. When I change the value of the referenced cell I get the following error:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
ruta = ThisWorkbook.Path & ""
arch = "C:Desktop\PCL Load Billing 11.29.2018.xlsx"
Application.ScreenUpdating = False
Set 12 = Workbooks.Open(ruta & arch)
12.Sheets(1).Range("A2").Value = Target.Value
12.Save
12.Close
End If
End Sub

On Debug the line that begins with Set 12 is yellow. Not sure what the issue is.

Tim

 

[DanteAmor]

"l2" is the letter l, not the number 1, however, I changed the letter l to wb


I also corrected the path

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If Target.Value = "" Then Exit Sub
        ruta = "C:\Desktop\"
        arch = "PCL Load Billing 11.29.2018.xlsx"
        Application.ScreenUpdating = False
        Set wb2 = Workbooks.Open(ruta & arch)
        wb2.Sheets(1).Range("A2").Value = Target.Value
        wb2.Save
        wb2.Close
    End If
End Sub

Try and tell me