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

Thank you. Have a great weekend. Speak to you on Monday

 

[Patriot2879]

Re: Ambigious name error please help

Hi good morning, just tried the code but unfortunately still the same test2 updates test1, but test1 doesn't update test2. the vba code in both test1 and test2 are exactly the same is that right?

 

[Patriot2879]

Re: Ambigious name error please help

Hi ZVI good morning, hope you had a great weekend. this is the updated code below

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim i As Long, j As Long, TargetValue
  Dim sPath As String, sFileName As String
 
  On Error GoTo exit_
  If Target.Address <> "$AC$6" Then Exit Sub
 
  ' Save Target.Value
  TargetValue = Target.Value
 
  ' Disable events handling
  Application.EnableEvents = False
 
  ' 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
 
  ' Update A2 current region in another workbook in the same folder
  sPath = ThisWorkbook.Path & "\"
  If LCase(ThisWorkbook.Name) = "test1.xlsm" Then
    sFileName = sPath & "test2.xlsm"
  Else
    sFileName = sPath & "test1.xlsm"
  End If
  With Workbooks.Open(sFileName)
    .Sheets(Me.Name).Range("A2").CurrentRegion.Value = Me.Range("A2").CurrentRegion.Value
    .Save
  End With
 
exit_:
 
  ' Restore events handlong
  Application.EnableEvents = True
 
  ' Inform about error
  If Err Then MsgBox Err.Description, vbCritical, "Error!"
 
End Sub

 

[ZVI]

Re: Ambigious name error please help

Hi,
This code works on 2 workbooks (test1.xlsm and test2.xlsm) based on your uploaded workbook.
Both workbooks should be stored in the same folder.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim a() As Variant, i As Long, j As Long, TargetValue
  Dim sPath As String, sFileName As String
 
  On Error GoTo exit_
  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
 
  ' Update A2 current region in another workbook in the same folder
  sPath = ThisWorkbook.Path & "\"
  If LCase(ThisWorkbook.Name) = "test1.xlsm" Then
    sFileName = sPath & "test2.xlsm"
  Else
    sFileName = sPath & "test1.xlsm"
  End If
  Application.ScreenUpdating = False
  a() = Me.Range("A2").CurrentRegion.Value
  With Workbooks.Open(sFileName, UpdateLinks:=False)
    .Sheets(Me.Name).Range("A2").CurrentRegion.Resize(UBound(a), UBound(a, 2)).Value = a()
    .Save
    '.Close
  End With
  Me.Parent.Activate
 
exit_:
 
  ' Restore events handlong and screen updating
  Application.EnableEvents = True
  Application.ScreenUpdating = True
 
  ' Inform about error
  If Err Then MsgBox Err.Description, vbCritical, "Error!"
 
End Sub

For testing on your side open only test1.xlsm and change AC6 in Sheet3.
Code modifies one cell in A2:R18 of Sheet3, opens test2.xlsm, copy data from [test1.xlsm]Sheet3!A2:R18 to the same range of test2.xlsm and saves test2.xlsm , workbook test1.xlsm is activated. Note: test1.xlsm is not saved.
If something is not as described above then please give us more details.

 

[Patriot2879]

Re: Ambigious name error please help

Hi, good morning i tried what you request nothing happened when in test1, when I try in test2 ow I get a subscript out of range, both files are saved on my desktop,do I need to save them in a folder on y desktop?

 

[ZVI]

Re: Ambigious name error please help

Hi, good morning i tried what you request nothing happened when in test1, when I try in test2 ow I get a subscript out of range, both files are saved on my desktop,do I need to save them in a folder on y desktop?

Good morning!
It does not matter where the folder is, desktop is a folder too.
Comment this: On Error GoTo exit_
What code line is yellowed after that by debugger with "subscript out of range" error?
Could you upload both workbooks for the testing?

 

[Patriot2879]

Re: Ambigious name error please help

Hiya I will upload them both now thank you
https://www.dropbox.com/s/cxoqsa5mnuju6tf/test2.xlsm?dl=0

https://www.dropbox.com/s/lc35bvsxrfivlpw/test1.xlsm?dl=0

 

[ZVI]

Re: Ambigious name error please help

Hmm, code of your uploaded workbooks works without problems.
May be workbooks was stored in the different folders of desktop...
Could you please put both workbooks in the same folder outside the desktop and test?
For example in C:\TEST folder
Note: AA3, AC3 and AC4 are not synchronized, do you mean this?

 

[Patriot2879]

Re: Ambigious name error please help

How odd, it is now working, I have put both files in a folder called Test saved on my desktop and it now works. Than you for all your help.

The 2 files will need to be saved in 2 different locations now please can you advise how I can change this in the coding.

 

[ZVI]

Re: Ambigious name error please help

Great!
Should go out now, I'll be back with a code for different folders in 2 hours