Copy range from one workbook to another

[dpaton05]

I have this code

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, wb1)
        wb2.Sheets(1).Range("A2:A" & wb2.Sheets(1).Range("A2").End(xlUp).Row).Copy wb1.Sheets("YPNames").Range("A2")
        
        wb1.Names.Add Name:="tblYPNames", RefersTo:=wb1.Sheets("YPNames").Range("A2").End(xlUp)
        Tracker.cboYP.ListFillRange = "tblYPNames"
        Tracker.cboYP.ListFillRange = "tblYPNames"
Application.DisplayAlerts = True
End Sub

Is this line the correct code to copy the list on sheet 1 of list.xlsm to the sheet called YPNames in the workbook that contains the sub?

wb2.Sheets(1).Range("A2:A" & wb2.Sheets(1).Range("A2").End(xlUp).Row).Copy wb1.Sheets("YPNames").Range("A2")

 

[Zot]

This line
wb2.Sheets(1).Range("A2:A" & wb2.Sheets(1).Range("A2").End(xlUp).Row)

You started with Range("A2") and then Ctrl+ArrowUp? You are not getting the range from Range("A2") and below, if that what you were trying to do.

 

[Zot]

Maybe something like this?
wb2.Sheets(1).Range("A2", wb2.Sheets(1).Cells(wb2.Rows.Count, "A").End(xlUp)).Copy wb1.Sheets("YPNames").Range("A2")

 

[dpaton05]

Thanks Zot. I tried adding some code and I have this now but I get the error on the line you provided that says Object doesn't support this property or method. I didn't think it was so hard to copy some data.

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)
    Call CheckFolderExists
    Call CheckFileExists
    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, wb1)
        wb2.Sheets(1).Range("A2", wb2.Sheets(1).Cells(wb2.Rows.Count, "A").End(xlUp)).Copy wb1.Sheets("YPNames").Range("A2")
        
        wb2.Save
        wb2.Close
        
        wb1.Names.Add Name:="tblYPNames", RefersTo:=wb1.Sheets("YPNames").Range("A2").End(xlUp)
        Tracker.cboYP.ListFillRange = "tblYPNames"
        Tracker.cboYP.ListFillRange = "tblYPNames"
Application.DisplayAlerts = True
End Sub

Sub CheckFileExists()

Dim strFileName As String
Dim strFileExists As String

    strFileName = ThisWorkbook.Path & "\Young People\List.xlsm"
    strFileExists = Dir(strFileName)

    If strFileExists = "" Then
        Workbooks.Add.SaveAs ThisWorkbook.Path & "\Young People\List", 52
    End If
End Sub


Sub CreateWB(newyp As String, wb1 As Workbook)
Dim V
    Call CheckFolderExists
    Workbooks.Add.SaveAs ThisWorkbook.Path & "\Young People\" & newyp, 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(6, 4) = ""
End Sub

Sub CheckFolderExists()
    If Dir(ThisWorkbook.Path & "\Young People", vbDirectory) = "" Then
        MkDir Path:=ThisWorkbook.Path & "\Young People"
    End If
End Sub

With the code I added, I was trying to write code that would check if the List file exists and if not, create it. The list file just needs to be a plain .xlsm file.

 

[dpaton05]

What have I done wrong?

 

[Michael M]

Maybe copy the whole column

wb2.Sheets(1).Columns("A:A").Copy wb1.Sheets("YPNames").Range("A1")

 

[dpaton05]

That works Michael, thank you!

 

[Michael M]

 

[dpaton05]

Just having another issue now. I need the new range that is copied to the sheet YPNames, to become the named range tblYPNames. As you can see from post 4, I have a line of code near the end that I thought would change the named range but it is not working. What is wrong with it?

 

[dpaton05]

wb1.Names.Add Name:="tblYPNames", RefersTo:=wb1.Sheets("YPNames").Range("A2").End(xlDown)