dpaton05
Well-known Member
- Joined
- Aug 14, 2018
- Messages
- 2,392
- Office Version
- 365
- 2016
- Platform
- Windows
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.
This line is red and I am not sure why
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?
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.
VBA Code:
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
VBA Code:
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?
Last edited: