Using user defined workbook name in macro worksheet functions

M

Mark

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add-in WITH worksheets?? 1
Editing recorded macro code. Referencing workbook name in functio 2
Problem in adding macro to ribbon through Add-in 7
User Defined Function being called by changes to an independent workbook 13
Excel 2010 Windows.Activate name issue 4
File name in macro 3
Range named Add_Info_** issue 0
Slow loading workbook 4

Top