Editing recorded macro code. Referencing workbook name in functio

[Mark]

I created the following code using the macro recorder. What I need to do is
find a way to replace the workbook name reference in the function with some
generic reference that will always look in the open workbook -- no matter
what the user names it.

I think I know how to do it, in terms of creating variables to reference the
workbook -- but I don't know how the syntax works in a worksheet function
like this.

Range("A64").Select
ActiveCell.FormulaR1C1 = _
"='[Functional Generic Estimating Model Loaded 1.1.xls]Summary
Data'!R[440]C20"

 

[David]

Hi Mark,

Not sure this will do exactly what you want, but you maybe able to play with
it and get what you want:
Sub Macro1()
Range("A64").Select
Z = ActiveWorkbook.Name
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]SummaryData '!R[440]C20"
End Sub

 

[Mark]

In all honesty, that is what I think I need. I just needed to know the
syntax for referencing the variable in the worksheet function in the macro.
Thanks.

Hi Mark,

Not sure this will do exactly what you want, but you maybe able to play with
it and get what you want:
Sub Macro1()
Range("A64").Select
Z = ActiveWorkbook.Name
ActiveCell.FormulaR1C1 = _
"='[" & Z & "]SummaryData '!R[440]C20"
End Sub

I created the following code using the macro recorder. What I need to do is
find a way to replace the workbook name reference in the function with some
generic reference that will always look in the open workbook -- no matter
what the user names it.

I think I know how to do it, in terms of creating variables to reference the
workbook -- but I don't know how the syntax works in a worksheet function
like this.

Range("A64").Select
ActiveCell.FormulaR1C1 = _
"='[Functional Generic Estimating Model Loaded 1.1.xls]Summary
Data'!R[440]C20"