Changing my spreadsheet to reference another spreadsheet instead of an internal sheet

[dpaton05]

I was recording the Young People in a separate sheet in my spreadsheet but I would like it to be in a separate document, so multiple people can access it at once.

I have started to try and change the code. The separate file is list.xlsm and is stored in the same directory as the Young people files.
There is nothing on the spreadsheet, except for a list of young people starting in A2 of the first sheet and going down.

I have tried to start to change the code and have ran into a few problems.

Sub AddYP()
Application.DisplayAlerts = False
Dim newyp As String, rng As Range, wb1 As Workbook, wb2 As Workbook
    Set wb1 = ThisWorkbook
        newyp = Tracker.Cells(6, 4)
    Workbooks.Open Filename:=ThisWorkbook.Path & "\Young People\List.xlsm"
    Set wb2 = Workbooks("List")
    With wb2.Sheets(1).Range("A:A") 'searches all of column A
        Set rng = .Find(What:=newyp, After:=.Cells(.Cells.Count), LookIn:=xlValues, _
                        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not rng Is Nothing Then
        MsgBox "This name is already in the list."
        Exit Sub
        Else
            wb2.Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = newyp
       End If
        End With
        Call CreateWB(newyp)
        wb2.sheets(1).range("A2:A" & wb2.Sheets(1).range("A2").end(xldown).row & ")"
    ThisWorkbook.Names.Add Name:="tblYPNames", _
    RefersTo:=Range("tblYPNames").Resize(Range("tblYPNames").Rows.Count + 1)
        Tracker.cboYP.ListFillRange = "tblYPNames"
        Tracker.cboYP.ListFillRange = "tblYPNames"
Application.DisplayAlerts = True
End Sub

This line is red and I am not sure why

wb2.sheets(1).range("A2:A" & wb2.Sheets(1).range("A2").end(xldown).row & ")"

What I am trying to do is copy the list from the list workbook and paste it into the sheet YP each time I run the sub so it is always up to date.

Could someone help me please?

 

[Zot]

Should the line just be like
wb2.Sheets(1).Range ("A2:A" & wb2.Sheets(1).Range("A2").End(xlDown).Row)

 

[dpaton05]

Thanks Zot.

 

[dpaton05]

When the new range is copied across from the list workbook to the current workbook, I then need to define a new named range. What is wrong with my code as it tells me there is a syntax error

Sub AddYP()
Application.DisplayAlerts = False
Dim newyp As String, rng As Range, wb1 As Workbook, wb2 As Workbook, ListName As String
    Set wb1 = ThisWorkbook
        newyp = Tracker.Cells(6, 4)
    Workbooks.Open Filename:=ThisWorkbook.Path & "\Young People\List.xlsm"
    Set wb2 = Workbooks("List")
    With wb2.Sheets(1).Range("A:A") 'searches all of column A
        Set rng = .Find(What:=newyp, After:=.Cells(.Cells.Count), LookIn:=xlValues, _
                        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not rng Is Nothing Then
        MsgBox "This name is already in the list."
        Exit Sub
        Else
            wb2.Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = newyp
       End If
        End With
        Call CreateWB(newyp)
        wb2.Sheets(1).Range("A2:A" & wb2.Sheets(1).Range("A2").End(xlDown).Row).Copy wb1.Sheets("YPNames").Range("A2")
        
        wb1.Names.Add Name:="tblYPNames",
        RefersTo:=Range("tblYPNames").Resize(Range("tblYPNames").Rows.Count + 1)
        Tracker.cboYP.ListFillRange = "tblYPNames"
        Tracker.cboYP.ListFillRange = "tblYPNames"
Application.DisplayAlerts = True
End Sub

The error is with this bit

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

 

[Zot]

You wrote in two line, thus you need to add underscore after Name:="tblYPNames",

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

 

[dpaton05]

Thanks Zot