Code name not working

[dpaton05]

I have some code that opens a workbook and I then used to code name of the sheet in the opened workbook but it highlights the code name and says variable not defined.

This is the sub I try and run

Sub AddYP()
Application.DisplayAlerts = False
Dim newyp As String, wb1 As Workbook
    'Assign the name in D5 of the Tracker sheet to the variable newyp
    newyp = Tracker.Cells(5, 4)
    'Check if List file is open and if not, open it
    If Not isFileOpen("List.xlsm") Then
        Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\" & "Young People" & "\" & "List.xlsm")
    End If
    With ActiveWorkbook
        'Offset from the bottom of the list of YP by 1 to add the newyp to the list
        NamesList.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = newyp
            'Call CreateWB sub and send the variable newyp to it
            Call CreateWB(newyp)
        'Define a new named range for tblYPNames by resizing the current range by _
        one row down
        ThisWorkbook.Names.Add Name:="tblYPNames", _
        RefersTo:=NamesList.Range("tblYPNames").Resize(NamesList.Range("tblYPNames").Rows.Count + 1)
            Tracker.cboYP.ListFillRange = "tblYPNames"
    End With
    'Sort the YP names list in alphabetical order
    Call Sort
Application.DisplayAlerts = True
End Sub

Could someone help me please?

 

[Michael M]

If the code referss to wb1, you will need

wb2.Names.Add Name:="tblYPNames", _
        RefersTo:=wb1.Range("tblYPNames").Resize(wb1.Range("tblYPNames").Rows.Count + 1)

 

[dpaton05]

I forgot to mention the error is
method range of object global failed

 

[dpaton05]

If the code referss to wb1, you will need

wb2.Names.Add Name:="tblYPNames", _
        RefersTo:=wb1.Range("tblYPNames").Resize(wb1.Range("tblYPNames").Rows.Count + 1)

The named range and the list of names is in wb2

 

[Alex Blakenburg]

We are certainly doing this in dribs and drabs.

I am assuming the With statement is supposed to refer to the List workbook so change it back to wb2.
Assuming the range is meant to refer to wb2 then unless you put a "." in front of the Range the With is not doing anything.

    With wb2.Sheets(1)
        'Offset from the bottom of the list of YP by 1 to add the newyp to the list
        .Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = newyp
            'Call CreateWB sub and send the variable newyp to it
            Call CreateWB(newyp, wb1)
    End With

 

[Alex Blakenburg]

Oops a full stop in front of Rows.Count too. (since the default if the active workbook which wb2 is at that point, it shouldn't make a difference but it will be clearer)

 

[dpaton05]

This is what I have now.

Sub AddYP()
Application.DisplayAlerts = False
Dim newyp As String, wb1 As Workbook, wb2 As Workbook
    'Assign the name in D5 of the Tracker sheet to the variable newyp
    newyp = Tracker.Cells(5, 4)
    'Check if List file is open and if not, open it
    Set wb1 = ThisWorkbook
    If Not isFileOpen("List.xlsm") Then
        Set wb2 = Workbooks.Open(ThisWorkbook.Path & "\" & "Young People" & "\" & "List.xlsm")
    End If
    Set wb2 = Workbooks("List")
    With wb2.Sheets(1)
        'Offset from the bottom of the list of YP by 1 to add the newyp to the list
        .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0) = newyp
            'Call CreateWB sub and send the variable newyp to it
            Call CreateWB(newyp, wb1)
    End With
    
    
        'Define a new named range for tblYPNames by resizing the current range by _
        one row down
        wb2.Names.Add Name:="tblYPNames", _
        RefersTo:=wb2.Worksheets(1).wb2.Range("tblYPNames").Resize(Range("tblYPNames").Rows.Count + 1)
        wb1.Worksheets("Tracker").cboYP.ListFillRange = "tblYPNames"
    
    'Sort the YP names list in alphabetical order
    Call Sort
Application.DisplayAlerts = True
End Sub

I am not feeling that great today so my ability to create meaningful code is not that good.

With this code, I get the error Method range of object global failed.
The following code is highlighted

        wb2.Names.Add Name:="tblYPNames", _
        RefersTo:=wb2.Worksheets(1).wb2.Range("tblYPNames").Resize(Range("tblYPNames").Rows.Count + 1)

I think the error occurred as there is no named range already defined in the list workbook.

I also want to add in code to create the list file if it does not exist.

 

[Alex Blakenburg]

I changed and added this:-

Dim ws2 as Worksheet
Set ws2 = wb2.Sheets(1)
    
        wb2.Names.Add Name:="tblYPNames", _
        RefersTo:=ws2.Range("tblYPNames").Resize(ws2.Range("tblYPNames").Rows.Count + 1)
        wb1.Worksheets("Tracker").cboYP.ListFillRange = ws2.Range("tblYPNames")

 

[mikerickson]

If you have a workbook and the codename of a worksheet, this UDF will return that sheet

Function WorksheetCodeNamed(aCodeName As String, Optional wb As Workbook) As Worksheet
    If wb Is Nothing Then Set wb = ThisWorkbook
    With wb
        Set WorksheetCodeNamed = .Sheets(.VBProject.VBComponents(aCodeName).Properties("Index"))
    End With
End Function