Ambiguous name error

P

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,259
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
 
Sort by date Sort by votes
Re: Ambigious name error please help

Hi yes james006 has been helping me
Click to expand...
Is it the same issue? If so, then you should really stick to one thread, and let him continue helping you (we do not want multiple threads on the same issue).
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Re: Ambigious name error please help

Hi the one code is for 2 workbooks and I need these combined together as 2 different people will be using each book but when someone does an update it automatically updates the other workbook and Visa versa. The other code is if someone inputs a number in ac6 these number gets deducted from the total by cross referencing the selected range. I hope this makes sense.
 
Upvote 0
Re: Ambigious name error please help

Hi no not same issue. But when I tried putting 2 codes on the same sheet1 they didnt work together
 
Upvote 0
Re: Ambigious name error please help

I am not available tomorrow morning, but if you post some sample data, along with an explanation of all the different things you want done, I can take a look at it when I have a chance.
 
Upvote 0
Re: Ambigious name error please help

Ok thank you joe4 I will send the information tomorrow thank you for the advice
 
Upvote 0
Re: Ambigious name error please help

Hi again :), please see my notes below and the attachment :).
I have 2workbooks called test2 and test1, theseboth have the same data, for example in cell C12 is the number 13 in bothworkbooks, but if someone reduces this number to 11 in either of the workbooksi want the other workbook to automatically update the cell to 11, i dont wantto mirror the workbook as i want them both live.

In AC3 I havethe following formula that checks the area for a postcode which is entered intocell AA3 =VLOOKUP(AA3,Sheet2!A:B,2,FALSE)
In AC4 I havea list for dates that is the range from B2:R2
In AC5 I havea formula to lookup the available slots which matches the date/area
=IFERROR(INDEX($A$1:$R$18,MATCH(AC3,$A$1:$A$18,0),MATCH(AC4,$A$2:$R$2, 0)),"")
In AC6 thisis where you can select how many you want to book from the availability – whichwhen inputted and entered this will deduct from the availability and alsocrossreference from the range B3:R18 and deduct as well.

I have thex2 codes that work separately but they don’t work together, can you help meplease to put the x2 codes into one?

Hope you can help

https://www.dropbox.com/s/cxmlv37hnw41l9s/test2.xlsm?dl=0





 
Upvote 0
Re: Ambigious name error please help

these are the 2 codes that I want combining please :)
Code: 
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][INDENT][FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]1.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]PrivateSub Worksheet_Change(ByVal Target As Range)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]2.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]Dimi As Long, j As Long, errorFound[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]3.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]OnError GoTo errorFound[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]4.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]IfTarget.Address <> "$AC$6" Then Exit Sub[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]5.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]Application.EnableEvents= False[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]6.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    ' Determine Row # and Column #[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]7.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    i =Application.Match(Range("AC3"), Range("A1:A18"), 0)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]8.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    j =Application.Match(Range("AC4"), Range("A2:R2"), 0)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]9.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]10. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    ' Adjust the Intersection cell Value bysubstracting Input in AC6[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]11. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Cells(i, j).Value = Cells(i, j).Value -Target[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]12. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]13. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    ' Clear ONLY Target cell[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]14. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Target.ClearContents[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]15. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]                [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]16. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]errorFound:[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]17. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]Application.EnableEvents= True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]18. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]EndSub[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]19. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]PrivateSub Worksheet_Change(ByVal Target As Range)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]20. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    'This code goes in Book13, amend for book14[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]21. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]22. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]23. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    'this sub requires both workbooks to beopen[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]24. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Dim wb As Workbook[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]25. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Dim spath As String, sFileName As String[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]26. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Dim bOpen As Boolean 'remember if workbookwas open or not[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]27. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]28. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    spath ="C:\Users\s21375\Desktop\"[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]29. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]30. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    ' *** This line needs changing for Book 14[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]31. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    sFileName = "Test1.xlsm"[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]32. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]33. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    On Error Resume Next[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]34. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Set wb = Workbooks(sFileName)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]35. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    If wb Is Nothing Then[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]36. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]        Set wb = Workbooks.Open(spath &sFileName)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]37. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]        If wb Is Nothing Then[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]38. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]            MsgBox "File can not beopened", vbCritical[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]39. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]            Exit Sub[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]40. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]        End If[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]41. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Else[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]42. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]        bOpen = True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]43. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    End If[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]44. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    On Error GoTo 0[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]45. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]46. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Application.EnableEvents = False[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]47. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Application.ScreenUpdating = False[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]48. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]49. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    wb.Sheets(Me.Name).Range(Target.Address) =Target[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]50. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    If bOpen = False Then[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]51. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]        wb.Save[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]52. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    End If[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]53. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Application.EnableEvents = True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]54. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Application.ScreenUpdating = True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]55. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]56. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]57. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    If Not Intersect(Target,Me.Range("A2:R18")) Is Nothing Then[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]58. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]        ThisWorkbook.Save[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]59. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    End If[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][/INDENT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Times New Roman"]End Sub[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][INDENT][FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]1.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]PrivateSub Worksheet_Change(ByVal Target As Range)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]2.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    'This code goes in Book13, amend for book14[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]3.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]4.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]5.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    'this sub requires both workbooks to beopen[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]6.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Dim wb As Workbook[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]7.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Dim spath As String, sFileName As String[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]8.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Dim bOpen As Boolean 'remember if workbookwas open or not[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]9.  [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]10. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    spath ="C:\Users\s21375\Desktop\"[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]11. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]12. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    ' *** This line needs changing for Book 14[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]13. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    sFileName = "Test1.xlsm"[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]14. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]15. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    On Error Resume Next[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]16. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Set wb = Workbooks(sFileName)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]17. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    If wb Is Nothing Then[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]18. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]        Set wb = Workbooks.Open(spath &sFileName)[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]19. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]        If wb Is Nothing Then[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]20. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]            MsgBox "File can not beopened", vbCritical[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]21. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]            Exit Sub[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]22. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]        End If[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]23. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Else[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]24. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]        bOpen = True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]25. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    End If[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]26. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    On Error GoTo 0[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]27. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]28. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Application.EnableEvents = False[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]29. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Application.ScreenUpdating = False[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]30. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]31. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    wb.Sheets(Me.Name).Range(Target.Address) =Target[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]32. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    If bOpen = False Then[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]33. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]        wb.Save[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]34. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"] [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]35. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    End If[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]36. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Application.EnableEvents = True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]37. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    Application.ScreenUpdating = True[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]38. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]39. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    [/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]40. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    If Not Intersect(Target,Me.Range("A2:R18")) Is Nothing Then[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]41. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]        ThisWorkbook.Save[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Courier New"]42. [/FONT][/COLOR][COLOR=#574123][FONT="Courier New"]    End If[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][/INDENT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#574123][FONT="Times New Roman"]End Sub[/FONT][/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/CO[/COLOR][/SIZE][/FONT]DE]
 
Upvote 0
Re: Ambigious name error please help

hope you can help, hopefully I have explained it all enough, both codes do work great on there own but not combined together which I now need them to be.
 
Upvote 0
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
 
Upvote 0
Re: Ambigious name error please help

Hi,
Try this template of the code (for your post #4):
Rich (BB code): 
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
  ' ... your code 1 ...
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
  ' ... your code 2 ...
End Sub
For the code of post #17 just add one more part - Worksheet_Change3

Regards
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Help with Code for Ambiguous Error
Replies
3
Views
76
Fuji
F
P
  • Question Question
Emailing 1 sheet
Replies
6
Views
265
Micron
M
B
  • Solved
Error on method intersect
Replies
2
Views
136
Beneindias
B
O
  • Question Question
Creating workbooks for a team of staff
Replies
1
Views
211
HighAndWilder
H
D
  • Question Question
Make the code work in a protected sheet
Replies
7
Views
195
dmt32
D

Forum statistics

Threads
1,224,828
Messages
6,181,209
Members
453,023
Latest member
alabaz

Share this page

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