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")

 

[Michael M]

doesn't the code in AddYP already do that ??

wb1.Names.Add Name:="tblYPNames", RefersTo:=wb1.Sheets("YPNames").Range("A2").End(xlUp)
        Tracker.cboYP.ListFillRange = "tblYPNames"

 

[Michael M]

OR this

wb1.Names.Add Name:="tblYPNames", RefersTo:=wb1.Sheets("YPNames").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        Tracker.cboYP.ListFillRange = "tblYPNames"

 

[dpaton05]

How can I set the named range as the current list in YPNames, starting at A2?

 

[Michael M]

Doesn't post #12 do that ??

 

[dpaton05]

I have the named range pointing to A2, add a name and then the named range is pointing to A1:A2, instead of A2:A3.

 

[Michael M]

Is wb1 the activeworkbook ??
If so

Sheets("YPNames").Names.Add Name:="tblYPNames", RefersTo:=Sheets("YPNames").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        Tracker.cboYP.ListFillRange = "tblYPNames"

 

[dpaton05]

I changed your code to xldown instead of xlup and the whole list was in the named range but the named range now refers to this

=YPNames!$A$2:$A$1048576

 

[Michael M]

That's because you used XLDOWN

 

[dpaton05]

That seems to be working pretty well now, thanks Michael.

Is there a way Michael to read the file names that are in the folder, Young People, without the extension into the combo box cboYPNames instead of having a separate list file for them?

So the combo will always show all of the files that are in the folder and will be up to date?

 

[Michael M]

Have a read through this thread...

VBA Copy All Filenames In Folder

All, I have a directory with around 100+ files, what I need to do it copy all the filenames into excel. Hope you can help!

www.mrexcel.com

but by continuously changing the way you do things will simply cause you lots of headaches....you'll simply get confused.
AND
I don't think you will extract straight to the combo box