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

great, thank you again for all your help, speak to you soon

 

[ZVI]

Re: Ambigious name error please help

Below is the code version for workbooks stored in different folders declared by constants FullName1 and FullName2.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  '--> User settings, change to suit
  Const FullName1 = "C:\Users\s21375\Desktop\Test1\test1.xlsm"
  Const FullName2 = "C:\Users\s21375\Desktop\Test2\test2.xlsm"
  '<-- End of the settings
 
  Dim a() As Variant, i As Long, j As Long, TargetValue
  Dim sThisFullName 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
  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
  With Workbooks.Open(sFileName, UpdateLinks:=False)
    .Sheets(Me.Name).Range("A2").CurrentRegion.Resize(UBound(a), UBound(a, 2)).Value = a()
    .Save
    '.Close
  End With
 
exit_:
 
  ' Restore events handlong and screen updating
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  ThisWorkbook.Activate
 
  ' Inform about error
  If Err Then MsgBox Err.Description, vbCritical, "Error!"
 
End Sub

Regards

 

[Patriot2879]

Re: Ambigious name error please help

Great thanks I will give it a go in the morning

 

[Patriot2879]

Re: Ambigious name error please help

Hi ZVI good morning, I think I have gone a bit wrong on putting the path in as it has all gone red, I have changed test1 to test11 and test2 to test22 plase see code belowwhere I have added th paths,please can you advise where I went wrong.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  '--> User settings, change to suit
  Const FullName1 = "C:\Users\s21375\Desktop\Test1\test1.xlsm"
  Const FullName2 = "C:\Users\s21375\Desktop\Test2\test2.xlsm"
  '<-- End of the settings
 
  Dim a() As Variant, i As Long, j As Long, TargetValue
  Dim sThisFullName 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
  Target.Select
 
  ' Update A2 current region in another workbook in the same folder
  sThisFullName = ThisWorkbook.FullName
  If LCase(sThisFullName) = LCase C:\Users\s21375\Desktop\test2\test11.xlsm Then
    sFileName = G:\TEAM\ES_Mtr_Tech_Ops\Customer Management Centre\Utilisation\Complex\test\test22.xlsm
  Else
    sFileName = C:\Users\s21375\Desktop\test2\test11.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
 
exit_:
 
  ' Restore events handlong and screen updating
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  ThisWorkbook.Activate
 
  ' Inform about error
  If Err Then MsgBox Err.Description, vbCritical, "Error!"
 
End Sub

 

[ZVI]

Re: Ambigious name error please help

Hi,

Store both workbooks anywhere you want.

Activate in Excel one of them, in VBE Immediate window type: ?ActiveWorkbook.FullName
and then press Return key. Copy a full name appeared in Immediate window and paste it in the code as value of one of the constants, for example like this:
Const FullName1 = "C:\Users\s21375\Desktop\Test1\test11.xlsm"
In Red is what is appeared in the Immediate window

Do the same with another workbook, for example like this:
Const FullName2 = "C:\Users\s21375\Desktop\Test2\test12.xlsm"

Hope this will help.
Regards

 

[Patriot2879]

Re: Ambigious name error please help

hiya still abit confused I have changed to the following but still getting errors.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  '--> User settings, change to suit
  Const FullName1 = "C:\Users\s21375\Desktop\Test1\test1.xlsm"
  Const FullName2 = "C:\Users\s21375\Desktop\Test2\test2.xlsm"
  '<-- End of the settings
 
  Dim a() As Variant, i As Long, j As Long, TargetValue
  Dim sThisFullName 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
  Target.Select
 
  ' Update A2 current region in another workbook in the same folder
  sThisFullName = ThisWorkbook.FullName
  If LCase(sThisFullName) = LCase "C:\Users\s21375\Desktop\Test1\test11.xlsm" Then
    sFileName = Const FullName2 = "C:\Users\s21375\Desktop\Test2\test12.xlsm"
  Else
    Const FullName1 = "C:\Users\s21375\Desktop\Test1\test11.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
 
exit_:
 
  ' Restore events handlong and screen updating
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  ThisWorkbook.Activate
 
  ' Inform about error
  If Err Then MsgBox Err.Description, vbCritical, "Error!"
 
End Sub

 

[Patriot2879]

Re: Ambigious name error please help

Hi Sorry this is where the 2 files are saved on code above I put in same place. Test11 was previously test1 and test22 was previously test2

G:\TEAM\ES_Mtr_Tech_Ops\Customer ManagementCentre\Utilisation\Complex\test\test22.xlsm
C:\Users\s21375\Desktop\test2\test11.xlsm

 

[ZVI]

Re: Ambigious name error please help

hiya still abit confused I have changed to the following but still getting errors.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  '--> User settings, change to suit
  Const FullName1 = "C:\Users\s21375\Desktop\Test1\test1.xlsm"
  Const FullName2 = "C:\Users\s21375\Desktop\Test2\test2.xlsm"
  '<-- End of the settings

  '...
 
End Sub

What is in the error message?
In the previous post test11 and test12 were mentioned but it is not reflected in the code/
Well, for the testing use test1.xlsm in C:\Users\s21375\Desktop\Test1 folder and test2.xlsm in C:\Users\s21375\Desktop\Test2 folder., does it work?

 

[ZVI]

Re: Ambigious name error please help

Hi Sorry this is where the 2 files are saved on code above I put in same place. Test11 was previously test1 and test22 was previously test2

G:\TEAM\ES_Mtr_Tech_Ops\Customer ManagementCentre\Utilisation\Complex\test\test22.xlsm
C:\Users\s21375\Desktop\test2\test11.xlsm

Do you have permission to write in both folders?
What is in the error message?

 

[Patriot2879]

Re: Ambigious name error please help

hi the line goes red straight away for the code below, so not sure if I did it correctly. Yes I have permission for both folders

If LCase(sThisFullName) = LCase "C:\Users\s21375\Desktop\Test1\test11.xlsm" Then