Ambiguous name error

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,227
Office Version
  1. 2010
Platform
  1. Windows
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
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Code:
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
 
Thank you for all your help. Please one more thing please can you advise how I can add another workbook to this code please?
You are welcome. For one more thing:
1. Put fullnames of all synchronized workbooks into Sheet2!D2 and the below cells like this:
Book1
D
1Filenames of the sycnchronized workbooks
2C:\Users\s21375\Desktop\test1\test1.xlsm
3C:\Users\s21375\Desktop\test2\test11.xlsm
4C:\Users\s21375\Desktop\Test2.2\test22.xlsm
Sheet2


2. Use this code in Sheet3 code module:
Rich (BB code):
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim a() As Variant, i As Long, j As Long
  Dim sThisFullName As String, sSynchronized As String
  Dim Wb As Workbook, IsOpen As Boolean
  Dim FullName As Variant, FullNames As Range
 
  If Target.Address <> "$AC$6" Or Target.Value = "" Then Exit Sub
 
  ' Determine Row # and Column #
  i = Application.Match(Range("AC3"), Range("A1:A18"), 0)
  If i = 0 Then MsgBox Range("AC4") & " not found in A1:A18", vbCritical: Exit Sub
  j = Application.Match(Range("AC4"), Range("A2:R2"), 0)
  If i = 0 Then
    MsgBox Range("AC3").Value & " not found in A1:A18", vbCritical
    Exit Sub
  ElseIf j = 0 Then
    MsgBox Range("AC4").Value & " not found in A2:R2", vbCritical
    Exit Sub
  End If
 
  ' Disable events handling, enable auto calculation
  Application.EnableEvents = False
  Application.Calculation = xlCalculationAutomatic
 
  ' Adjust the Intersection cell Value by substracting Input in AC6
  Cells(i, j).Value = Cells(i, j).Value - Target.Value
 
  ' Clear ONLY Target cell and select it
  Target.ClearContents
  Target.Select
 
  ' Disable blinking
  Application.ScreenUpdating = False
 
  ' Update A2 current region in synchronized workbooks listed in Sheet2!D2:D...
  With Sheet2
    Set FullNames = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
  End With
  i = UBound(FullNames.Value) - 1
  j = 0
  sThisFullName = LCase(ThisWorkbook.FullName)
  a() = Me.Range("A2").CurrentRegion.Value
  For Each FullName In FullNames.Value
    If InStr(FullName, "\") > 0 And LCase(FullName) <> sThisFullName Then
      j = j + 1
      Application.StatusBar = "Updating (" & j & "/" & i & "): " & FullName
      On Error Resume Next
      Set Wb = Workbooks(Mid(FullName, InStrRev(FullName, "\") + 1))
      IsOpen = (Err = 0)
      On Error GoTo exit_
      If Not IsOpen Then
        Set Wb = Workbooks.Open(FullName, UpdateLinks:=False)
      End If
      With Wb
        .Sheets(Me.Name).Range("A2").CurrentRegion.Resize(UBound(a), UBound(a, 2)).Value = a()
        .Save
        If Not IsOpen Then .Close False
      End With
      sSynchronized = sSynchronized & IIf(j > 1, vbLf, "") & FullName
    End If
  Next
  ThisWorkbook.Activate
  
exit_:
 
  ' Restore events handling, screen updating and status bar
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Application.StatusBar = False
 
  ' Inform about error
  If Err Then
    MsgBox Err.Description, vbCritical, "Error!"
  Else
    ' Put updating info in the comment of AC6
    If Target.Comment Is Nothing Then Target.AddComment
    With Target.Comment
      .Visible = True
      .Text Text:="[Updated " & j & " workbook(s) on " & Now & "]" & vbLf & sSynchronized
      .Shape.TextFrame.AutoSize = True
      .Shape.TextFrame.AutoSize = False
    End With
  End If
 
End Sub
3. Repeat points 1 and 2 for all synchronized workbooks.
 
Last edited:
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi thank you for the new code it works great, the only thing is the availability cell doesn't update like it did before. but everything else works fab :). is there anyway to have the availability cell (AC5) to deduct like it did before still?
 
Upvote 0
Hi thank you for the new code it works great, the only thing is the availability cell doesn't update like it did before. but everything else works fab :). is there anyway to have the availability cell (AC5) to deduct like it did before still?
It means that some updated workbooks with formula in AC5 were previously saved with manual calculation option.
Insert this code line Application.Calculation = xlCalculationAutomatic before With Wb, like that:
Rich (BB code):
      Application.Calculation = xlCalculationAutomatic
      With Wb
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top