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 in AC4 I have a drop down list with dates which is from range B2:R18.

At the moment both workbooks are on my desktop but when finished they will be in different folders.

I can confirm that the goal of updating is in synchronization of current region A2:R18 for both workbooks.

In AC3 cell I have formula =VLOOKUP(AA3,Sheet2!A:B,2,FALSE) for a post code lookup

 

[ZVI]

Re: Ambigious name error please help

Please confirm that goal of updating is in synchronization of A2 current region (A2:R18 in your example) for both workbooks.

If the goal is as quoted one then:
1. Put this code in the same sheets of test1.xlsm and test2.xlsm which are stored in the same folder
2. If AC6 is changed then destination cell in data matrix A2:R18 is updated and values of A2:R18 are copied to the synchronized sheet of another workbook (test1 or test2).
3. There should be at least one empty column after the data matrix (A2:R18)

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 ThisWorkbook.Name = "test1.xlsm" Then
    sFileName = sPath & "test2.xlsm"
  Else
    sFileName = sPath & "test1.xlsm"
  End If
  Application.ScreenUpdating = False
  With Workbooks.Open(sFileName)
    .Sheets(Me.Name).Range("A2").CurrentRegion.Value = Me.Range("A2").CurrentRegion.Value
    .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

 

[Patriot2879]

Re: Ambigious name error please help

hi I have put this code in both workbooks thank you, when I test it in test2 it works great and duplicates the data into test1.
When I try in test1 it doesn't duplicate over to test2. Do I need to amend the code somehow in test1

 

[ZVI]

Re: Ambigious name error please help

I am now out of PC, be back in hours.
Activate test1 workbook and put this line in VBE Immediate window then press RETURN key:

?ActiveWorkbook.Name

The real name of workbook wil be printed in the line below, put it into the code instead of "test1.xlsm"

 

[Patriot2879]

Re: Ambigious name error please help

Hi it comes p with test1.xlsm

 

[ZVI]

Re: Ambigious name error please help

Replace this code line: If ThisWorkbook.Name = "test1.xlsm" Then
by that one: If LCase(ThisWorkbook.Name) = "test1.xlsm" Then

 

[Patriot2879]

Re: Ambigious name error please help

Hi ok will do thank you. I cant do until Monday morning now. Is it ok to let you know then how I get on please? Thank you as well for your help

 

[ZVI]

Re: Ambigious name error please help

Hi ok will do thank you. I cant do until Monday morning now. Is it ok to let you know then how I get on please? Thank you as well for your help

Hope it's ok now, let us know later on a testing result.
Synchronize yourself now with a good weekend!

 

[Patriot2879]

Re: Ambigious name error please help

Thank you. Hope you have a great weekend as well and thanks for all your help. For future reference when tested and all working okay I will need to save both workbooks in different folders will this be easy to amend?

 

[ZVI]

Re: Ambigious name error please help

...I will need to save both workbooks in different folders will this be easy to amend?

Sure, will amend it too later