I need to store my data in another workbook instead of another sheet in the same workbook

[dpaton05]

I have a spreadsheet that works fine. I adds names of young people to a list in another sheet. I want to change it to store the names in another workbook.

This is my code that works,

Sub AddYP()
Application.DisplayAlerts = False
Dim newyp As String
    newyp = Tracker.Cells(5, 4)
    YP.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = newyp
    Call CreateWB(newyp)
Application.DisplayAlerts = True
ThisWorkbook.Names.Add Name:="tblYPNames", _
RefersTo:=Range("tblYPNames").Resize(Range("tblYPNames").Rows.Count + 1)
End Sub

I wanted to add code that will open a spreadsheet to list the names in instead of another sheet but what is wrong with my code as it highlights the "&" in the 4th line and says type mismatch?

Sub AddYP()
Application.DisplayAlerts = False
Dim AddListWB As Workbook
Set AddListWB = ThisWorkbook.Path & "\Young People\List.xlsm"
Dim newyp As String
    newyp = Tracker.Cells(5, 4)
    YP.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = newyp
    Call CreateWB(newyp)
Application.DisplayAlerts = True
ThisWorkbook.Names.Add Name:="tblYPNames", _
RefersTo:=Range("tblYPNames").Resize(Range("tblYPNames").Rows.Count + 1)
End Sub

 

[Michael M]

you do realise that this will affect all your existing code that refers to the YPnames sheet ??
So there will be some rewriting of most of your original code.
I'm not interested in going down that path, but I would setup the new workbook in the required folder, then copy the range from YPNames across to the new workbook
I'm getting the feeling your boss is just pushing your buttons !!!

Sub MM1()
Dim a As Workbook, b As Workbook
Dim  rng as Range
Set a =Activeworkbook
Set b = Workbooks.Open(" path to destination book ")
rng = a.Sheets("YPNames").Range("A1:A1000").copy b.Sheets("sheetname").Range("A1")
b.Close
End Sub

 

[Zot]

you do realise that this will affect all your existing code that refers to the YPnames sheet ??
So there will be some rewriting of most of your original code.
I'm not interested in going down that path, but I would setup the new workbook in the required folder, then copy the range from YPNames across to the new workbook
I'm getting the feeling your boss is just pushing your buttons !!!

Sub MM1()
Dim a As Workbook, b As Workbook
Dim  rng as Range
Set a =Activeworkbook
Set b = Workbooks.Open(" path to destination book ")
rng = a.Sheets("YPNames").Range("A1:A1000").copy b.Sheets("sheetname").Range("A1")
b.Close
End Sub

That would be the best option I believe.

 

[dpaton05]

Ok, so he got feedback from other regions that will be using the tool. The problem was that if someone opened the workbook, added a young person then closed it without saving it, the young person file would still be added but if they reopened the spreadsheet, the combo box would not have the added name in it. But I think I might have worked out how to do it.


I have found some code that will list all the files in the folder that I want to include in the list.

Sub LoopThroughFiles()
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim i As Integer
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(ThisWorkbook.Path & "\Young People")
    For Each oFile In oFolder.Files
        YP.Cells(i + 1, 1) = oFile.Name
        i = i + 1
    Next oFile
End Sub

This code will add a list of the current names starting in A1 on the sheet where the names were but I need to named range to update as it overwrites it. I tried to add this code to create the named range again which I put just before the end sub but there is a syntax error with it. What did I do wrong with this bit of code?

ThisWorkbook.Names.Add Name:="tblYPNames", _
RefersTo:=Range("tblYPNames").Resize(YP.Range("A1:") & ("A1").end(xlup))

 

[Michael M]

So, simply add a workbook_Beforeclose event to save the workbook....problem solved!!
OR
add a line of code in the exisiting AddYP code to save the workbook after the new name is added

 

[dpaton05]

This is in thisworkbook module

Private Sub Workbook_close()
    ThisWorkbook.Save
End Sub

but it doesn't save anything. Have I done something wrong?

 

[dpaton05]

I realised I needed a Before infront of the close but that still gives an error. I get a compile error highlighting this

Private Sub Workbook_Beforeclose

 

[Michael M]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 If Me.Saved = False Then Me.Save
End Sub

 

[dpaton05]

That seems to have fixed it, thanks Michael