Using user defined workbook name in macro worksheet functions
 

I have a number of macros that work to create reports based off of a model I
have built. I want users to be able to run this macro and have it create a
separate workbook where the report is generated. To create the report, I
have a number of worksheet functions running in a macro.

Currently I have hard-coded workbook name in my macros. I want the macro to
actually look to see what the user has named the model file and generate the
report off that. The worksheet names will not change.

Below is a snipet of one portion of the report creating macro. Essentially
"functional generic estimating model loaded 1.1.xls" should be whatever the
user has named their file.


Sub Tech_Rates()

Range("AM64").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-38],'[Functional Generic Estimating Model Loaded
1.1.xls]Plan'!R2170C242:R2229C248,7,FALSE)"

Range("AM64").Select
Selection.AutoFill Destination:=Range("AM64:AM123"),
Type:=xlFillValues
Range("AM64:AM123").Select