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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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,
VBA Code:
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?
VBA Code:
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
You define AddListWB As Workbook.
ThisWorkbook.Path & "\Young People\List.xlsm" is a string but you are trying to set it as object workbook.
 
Upvote 0
Ok, thanks Zot.

How would I open the file if it is not already open?
 
Upvote 0
I guess the obvious question is....why ??
Wouldn't be simpler to put it in a hidden worksheet in the same workbook
 
Upvote 0
I guess you are right Michael. I think I am trying to make it more complicated then it needs to be.
 
Upvote 0
Yep....Put it in a hidden sheet with a Password....you can then code it into to your AddYP code !
 
Upvote 0
Ok, thanks Zot.

How would I open the file if it is not already open?
I could not understand you whole code.
Once you created a new workbook, the new workbook will become active workbook and you can say
Set AddListWB = ActiveWorkbook.

Anyway, I don;t know why you need new workbook :)
 
Upvote 0
I have just received some feedback on the spreadsheet I have made. I have a drop down list that lists the names that are stored on another sheet but the list needs to be a list of the names of the young people files that are stored in the Young people folder directory.
 
Upvote 0
Could someone help me with the vba to achieve this please?
 
Upvote 0
Could someone help me with the vba to achieve this please?
So, you have a drop-down list but the list is in another workbook in a folder called Young People?

My guess is that you wanted to create a workbook above to store the list and put it in the Young People folder?

Actually, how is you process flow for your macro here.

Rich (BB code):
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,

What is the initial ThisWorkbook above refers to?
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top