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

oww is there any way I can change that? it does work manually but not with the AC6 cell where it updates the correct cell, with my first code, is there any way to tie them both in together?

 

[ZVI]

Re: Ambigious name error please help

Try this:

Option Explicit
 
Dim TargetValue
 
Private Sub Worksheet_Change(ByVal Target As Range)
  Call Worksheet_Change1(Target)
  Call Worksheet_Change2(Target)
End Sub
 
Private Sub Worksheet_Change1(ByVal Target As Range)
  Dim i As Long, j As Long, errorFound
  On Error GoTo errorFound
  If Target.Address <> "$AC$6" Then Exit Sub
 
  ' --> ZVI
  ' Save Target.Value
  TargetValue = Target.Value
  '<--
 
  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 - Target
 
  ' Clear ONLY Target cell
  Target.ClearContents
 
errorFound:
  Application.EnableEvents = True
End Sub
 
Private Sub Worksheet_Change2(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
  '--> ZVI
  Application.EnableEvents = True
  wb.Sheets(Me.Name).Range(Target.Address) = TargetValue
  '<--
 
  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

What code is in test1.xlsm?

 

[Joe4]

Re: Ambigious name error please help

Hi good evening. I hope you can help me please really dont know what to do. I hope you can open the attachment. Thank you

Unfortunately, I never got a chance to log in yesterday (very busy day away from the computer). But it looks like ZVI has picked it up (thank you!) and is running with it. So I will let him continue on with it.

 

[Patriot2879]

Re: Ambigious name error please help

Hi thanks for your reply. The same code is in test1 I just changed the file name to test2

 

[ZVI]

Re: Ambigious name error please help

Hi thanks for your reply. The same code is in test1 I just changed the file name to test2

In this case infinite loop will occur: test2 updates test1 then test 1 updates test2 and so on.
Please confirm that goal of updating is in synchronization of A2 current region (A2:R18 in your example) for both workbooks.
If so, then code have to be amended.

 

[Patriot2879]

Re: Ambigious name error please help

Hi tried the new code excel crashes and restarts unfortunately when I put an update number in cell AC6

 

[ZVI]

Re: Ambigious name error please help

Hi tried the new code excel crashes and restarts unfortunately when I put an update number in cell AC6

See the reason in the 1st line of my previous post.
One more questiom: Are both workbooks stored in the same folder?

 

[Patriot2879]

Re: Ambigious name error please help

hi it works when I paste this into test2, but the dropdown list for the dates doesn't work when I select a date it comes up as 01/01/1900 for some reason

 

[Patriot2879]

Re: Ambigious name error please help

at the moment they are on my desktop but when finished they will be in different folders

 

[ZVI]

Re: Ambigious name error please help

hi it works when I paste this into test2, but the dropdown list for the dates doesn't work when I select a date it comes up as 01/01/1900 for some reason

Nothing was said before about dropdown list, please be more specific.
And please confirm/answer on my posts 35 and 37