Using macros to open another workbook automatically

Question

I am not very familiar with using macros, but I have a feeling it is going to be my only option for figuring out my issue.

Is it possible to use a macros to open a second spreadsheet when a first one (the one with the macros) is opened?  I'm trying to use 2 spreadsheets to incorporate drop down lists.  I want to maintain the master list myself and just have the other staff be able to use drop down lists in their own spreadsheets that pull data from the master list.  I've figured out how to create the drop down list in one spreadsheet when the information is in a master spreadsheet, but when the master spreadsheet is closed, the drop down list in the second spreadsheet no longer functions.  So I want to be able to write a macros in the spreadsheets that my staff use that will automatically open the master spreadsheet and update the drop down lists.

Is this possible?  And if so, how do I do this (remember - zero coding experience here!)?  Hopefully this makes sense.  If not, I can try and clarify.  I do not want to copy the information from the master list into the staff spreadsheets because there are way too many of them that I would have to update multiple times a year and it's just not feasible.

Answer 1

This works for me to open another excel book from one you're in already.

Sub open_new_wb()

 Dim newWB As Excel.Workbook

 Dim mytemplate As String

 mytemplate = "C:\documents and settings...\new_workbook.xlsx"  'replace this with actual workbook  address

 Application.Workbooks.Open (mytemplate)

End Sub

Answer 2

Press Alt+F11 to Open the VB Editor

From the Insert menu, Insert a new Module

Copy the paste the below code to the Module Code window

Press ALT + Q to close your VB Editor and return to Excel

ALT + F8 will give you list of all macros. Select and Click Run

Sub OpenWorkbook()

Workbooks.Open FileName:="C:\Users\UserName\Desktop\Book1.xlsm"

End Sub

Or you can give user an option to select his file from a directory, use the below code:

Sub OpenWorkbook()

filepath = Application.GetOpenFilename("Excel Files, *.xlsx")

If Not TypeName(filepath) = "Boolean" Then

    Workbooks.Open Filename:="C:\Users\UserName\Desktop\Book1.xlsm"

End If

End Sub