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

 

[Patriot2879]

Re: Ambigious name error please help

Hi I have it working now but it only updates when the other spreadsheet is closed, it doesn't update if the other spreadsheet is open, please can you advise?

 

[Patriot2879]

Re: Ambigious name error please help

Thank you for your help I have attached the 2 files again hope you can still help , I don't understand why it doesn’t update when both workbooks are open,only works if the other is closed.
https://www.dropbox.com/s/tzhy5pg7pnq5by8/test22.xlsm?dl=0
https://www.dropbox.com/s/v6vq9ohlom8x1iw/test11.xlsm?dl=0

 

[Patriot2879]

Re: Ambigious name error please help

Hope you can help do i need to add something In the code?

 

[ZVI]

Re: Ambigious name error please help

It is working.
In Excel open test11.xlsm only.
Change AC6, test12.xlsm opens, [test11.xlsm]Sheet3!A2:R18 copies to the [test22.xlsm]Sheet3!A2:R18
Switch between already open test11.xlsm and test12.xlsm , change AC6 in one or another workbook and see that range A2:R18 is synchronized (is the same) in both workbooks.

 

[ZVI]

Re: Ambigious name error

What is your Excel version?
You may also try this modification of the code, in all versions of Excel it restores an active workbook after synchronization regardless another workbook was already open or not:

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  '--> User settings, change to suit
  Const FullName1 = "C:\Users\s21375\Desktop\Test2.2\test22.xlsm"
  Const FullName2 = "C:\Users\s21375\Desktop\test2\test11.xlsm"
  '<-- End of the settings
 
  Dim a() As Variant, i As Long, j As Long, TargetValue
  Dim sThisFullName As String, sFileName As String
  Dim wb As Workbook, IsOpen As Boolean
 
  If Target.Address <> "$AC$6" Then Exit Sub
 
  ' Save Target.Value
  TargetValue = Target.Value
 
  ' Disable events handling
  Application.EnableEvents = False
  Application.Calculation = xlCalculationAutomatic
 
  ' Determine Row # and Column #
  i = Application.Match(Range("AC3"), Range("A1:A18"), 0)
  j = Application.Match(Range("AC4"), Range("A2:R2"), 0)
 
  ' Adjust the Intersection cell Value by substracting Input in AC6
  Cells(i, j).Value = Cells(i, j).Value - TargetValue
 
  ' Clear ONLY Target cell
  Target.ClearContents
  Target.Select
 
  ' Update A2 current region in another workbook in the same folder
  sThisFullName = ThisWorkbook.FullName
  If LCase(sThisFullName) = LCase(FullName1) Then
    sFileName = FullName2
  Else
    sFileName = FullName1
  End If
 
  Application.ScreenUpdating = False
  a() = Me.Range("A2").CurrentRegion.Value
  On Error Resume Next
  Set wb = Workbooks(Mid(sFileName, InStrRev(sFileName, "\") + 1))
  IsOpen = (Err = 0)
  On Error GoTo exit_
  If Not IsOpen Then
    Set wb = Workbooks.Open(sFileName, UpdateLinks:=False)
    Application.OnTime Now, Me.CodeName & ".ActivateMe"
  End If
 
  With wb
    .Sheets(Me.Name).Range("A2").CurrentRegion.Resize(UBound(a), UBound(a, 2)).Value = a()
    .Save
    '.Close
  End With
  
exit_:
 
  ' Restore events handling and screen updating
  Application.EnableEvents = True
  Application.ScreenUpdating = True
 
  ' Inform about error
  If Err Then MsgBox Err.Description, vbCritical, "Error!"
 
End Sub
 
Private Sub ActivateMe()
  Me.Parent.Activate
End Sub

 

[Patriot2879]

Re: Ambigious name error

Hi thank you for the code but I get an error now on the line below.

My version of excel is Office16, would this make adifference?

[FONT=Calibri][SIZE=3][COLOR=#000000]Cells(i, j).Value = Cells(i, j).Value – TargetValue[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/CO[/COLOR][/SIZE][/FONT]DE]

Sorry about this I am still new to all this and really appreciate your help.

 

[ZVI]

Re: Ambigious name error

... I get an error now on the line below.

[FONT=Calibri][SIZE=3][COLOR=#000000]Cells(i, j).Value = Cells(i, j).Value – TargetValue[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/CO[/COLOR][/SIZE][/FONT]DE][/QUOTE]
What is in error message?
May be there is no matches between [COLOR=#333333]AC3 and A1:A18 or AC4 and [/COLOR][COLOR=#333333]A2:R2[/COLOR]

 

[Patriot2879]

Re: Ambigious name error

hi with the new code it doesn't update either sheet unfortunately
.

is it because its Office16?

 

[Patriot2879]

Re: Ambigious name error

Hi this is very odd I have made 2 new folders on a friends computer and saved the 2 sheets and tested on theres and it works great, she has the same excel as me. Why is that do you think? thjis is very weird.

 

[Patriot2879]

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