dpaton05
Well-known Member
- Joined
- Aug 14, 2018
- Messages
- 2,352
- Office Version
- 365
- 2016
- Platform
- Windows
I have this code
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?
VBA 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?
VBA Code:
wb2.Sheets(1).Range("A2:A" & wb2.Sheets(1).Range("A2").End(xlUp).Row).Copy wb1.Sheets("YPNames").Range("A2")