Ambiguous name error

[Patriot2879]

Hi I have the code below but I get an ambiguous name error come up please can you help.
the error comes on the line below

Private Sub Worksheet_Change(ByVal Target As Range)

Private Sub Worksheet_Change(ByVal Target As Range)
    'This code goes in Book13, amend for book 14
    
    
    'this sub requires both workbooks to be open
    Dim wb As Workbook
    Dim spath As String, sFileName As String
    Dim bOpen As Boolean 'remember if workbook was open or not
    
    spath = "C:\Users\s21375\Desktop\"
    
    ' *** This line needs changing for Book 14
    sFileName = "Test1.xlsm"
    
    On Error Resume Next
    Set wb = Workbooks(sFileName)
    If wb Is Nothing Then
        Set wb = Workbooks.Open(spath & sFileName)
        If wb Is Nothing Then
            MsgBox "File can not be opened", vbCritical
            Exit Sub
        End If
    Else
        bOpen = True
    End If
    On Error GoTo 0
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    wb.Sheets(Me.Name).Range(Target.Address) = Target
    If bOpen = False Then
        wb.Save

    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    
    If Not Intersect(Target, Me.Range("A2:R18")) Is Nothing Then
        ThisWorkbook.Save
    End If
End Sub

 

[ZVI]

Thank you for all your help. Please one more thing please can you advise how I can add another workbook to this code please?

You are welcome. For one more thing:
1. Put fullnames of all synchronized workbooks into Sheet2!D2 and the below cells like this:

Book1
	D
1	Filenames of the sycnchronized workbooks
2	C:\Users\s21375\Desktop\test1\test1.xlsm
3	C:\Users\s21375\Desktop\test2\test11.xlsm
4	C:\Users\s21375\Desktop\Test2.2\test22.xlsm
Sheet2

2. Use this code in Sheet3 code module:

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim a() As Variant, i As Long, j As Long
  Dim sThisFullName As String, sSynchronized As String
  Dim Wb As Workbook, IsOpen As Boolean
  Dim FullName As Variant, FullNames As Range
 
  If Target.Address <> "$AC$6" Or Target.Value = "" Then Exit Sub
 
  ' Determine Row # and Column #
  i = Application.Match(Range("AC3"), Range("A1:A18"), 0)
  If i = 0 Then MsgBox Range("AC4") & " not found in A1:A18", vbCritical: Exit Sub
  j = Application.Match(Range("AC4"), Range("A2:R2"), 0)
  If i = 0 Then
    MsgBox Range("AC3").Value & " not found in A1:A18", vbCritical
    Exit Sub
  ElseIf j = 0 Then
    MsgBox Range("AC4").Value & " not found in A2:R2", vbCritical
    Exit Sub
  End If
 
  ' Disable events handling, enable auto calculation
  Application.EnableEvents = False
  Application.Calculation = xlCalculationAutomatic
 
  ' Adjust the Intersection cell Value by substracting Input in AC6
  Cells(i, j).Value = Cells(i, j).Value - Target.Value
 
  ' Clear ONLY Target cell and select it
  Target.ClearContents
  Target.Select
 
  ' Disable blinking
  Application.ScreenUpdating = False
 
  ' Update A2 current region in synchronized workbooks listed in Sheet2!D2:D...
  With Sheet2
    Set FullNames = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
  End With
  i = UBound(FullNames.Value) - 1
  j = 0
  sThisFullName = LCase(ThisWorkbook.FullName)
  a() = Me.Range("A2").CurrentRegion.Value
  For Each FullName In FullNames.Value
    If InStr(FullName, "\") > 0 And LCase(FullName) <> sThisFullName Then
      j = j + 1
      Application.StatusBar = "Updating (" & j & "/" & i & "): " & FullName
      On Error Resume Next
      Set Wb = Workbooks(Mid(FullName, InStrRev(FullName, "\") + 1))
      IsOpen = (Err = 0)
      On Error GoTo exit_
      If Not IsOpen Then
        Set Wb = Workbooks.Open(FullName, UpdateLinks:=False)
      End If
      With Wb
        .Sheets(Me.Name).Range("A2").CurrentRegion.Resize(UBound(a), UBound(a, 2)).Value = a()
        .Save
        If Not IsOpen Then .Close False
      End With
      sSynchronized = sSynchronized & IIf(j > 1, vbLf, "") & FullName
    End If
  Next
  ThisWorkbook.Activate
  
exit_:
 
  ' Restore events handling, screen updating and status bar
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Application.StatusBar = False
 
  ' Inform about error
  If Err Then
    MsgBox Err.Description, vbCritical, "Error!"
  Else
    ' Put updating info in the comment of AC6
    If Target.Comment Is Nothing Then Target.AddComment
    With Target.Comment
      .Visible = True
      .Text Text:="[Updated " & j & " workbook(s) on " & Now & "]" & vbLf & sSynchronized
      .Shape.TextFrame.AutoSize = True
      .Shape.TextFrame.AutoSize = False
    End With
  End If
 
End Sub

3. Repeat points 1 and 2 for all synchronized workbooks.

 

[Patriot2879]

Hi thank you for the new code it works great, the only thing is the availability cell doesn't update like it did before. but everything else works fab . is there anyway to have the availability cell (AC5) to deduct like it did before still?

 

[ZVI]

Hi thank you for the new code it works great, the only thing is the availability cell doesn't update like it did before. but everything else works fab . is there anyway to have the availability cell (AC5) to deduct like it did before still?

It means that some updated workbooks with formula in AC5 were previously saved with manual calculation option.
Insert this code line Application.Calculation = xlCalculationAutomatic before With Wb, like that:

      Application.Calculation = xlCalculationAutomatic
      With Wb