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]

Are you using Option Excplicit at the start of the codes ??
If so, it means you haven't declared the variable coe name ??
What line is it erroring on ?

 

[Alex Blakenburg]

Codename only works in the workbook with macro in it. (ThisWorkbook)

 

[dpaton05]

This is my code 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
    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)
    End With
        'Define a new named range for tblYPNames by resizing the current range by _
        one row down
        wb2.Names.Add Name:="tblYPNames", _
        RefersTo:=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 get the error now of object variable or with block variable not set with this line highlighted

With wb2.Sheets(1)

 

[Michael M]

Have you declared Wb2 ??

 

[dpaton05]

yep, it is in the 3rd line

 

[dpaton05]

Codename only works in the workbook with macro in it. (ThisWorkbook)

Thanks for that Alex

 

[dpaton05]

I changed the the line to

With Workbooks("List").Sheets(1)

and it works fine.

My code is 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
    With Workbooks("List").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:=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

Now when I run it, I get an error Method range of object global failed with this line highlighted

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

 

[dpaton05]

By the way, I am trying to move the list I had made on a separate sheet to a new workbook. I am not sure what parts of the code I will need to update.

 

[dpaton05]

The error is gone but now I get another error

This is my sub

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
    With Workbooks("List").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).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

It halts in this code and on the line

Workbooks.Add.SaveAs wb1.Path & "\Young People\" & newyp, FileFormat:=52

Sub CreateWB(newyp As String, wb1 As Workbook)
Dim V
    Call CheckFolderExists
    Workbooks.Add.SaveAs wb1.Path & "\Young People\" & newyp, FileFormat:=52
    For Each V In Split("7 8 9 10 11 12 1 2 3 4 5 6")
        Sheets.Add(, Sheets(Sheets.Count)).Name = MonthName(V)
    Next
    Call SetupSheets
    Sheets("sheet1").Delete
ActiveWorkbook.Close savechanges:=True
Tracker.Cells(5, 4).Clear
End Sub