Code To Insert Cell Reference
 

The following line opens the macro "Recalculate" in the file "Template.xls":
Application.Run "'Template.xls'!Recalculate"
The problem is the name of the file often changes. That change is recorded
in cell "C27" of worksheet "Customize". Can someone tell me the code to have
the line reference this cell? I've tried many variations of:
Application.Run "'" & Sheets("Customize").Range("C27").Value &
"'!Recalculate" with no luck. Thanks!

Code To Insert Cell Reference
 

Application.Run Sheets("Customize").Range("C27").Value&"!Recalcula te"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Carl Bowman" wrote in message
...
The following line opens the macro "Recalculate" in the file
"Template.xls":
Application.Run "'Template.xls'!Recalculate"
The problem is the name of the file often changes. That change is recorded
in cell "C27" of worksheet "Customize". Can someone tell me the code to
have
the line reference this cell? I've tried many variations of:
Application.Run "'" & Sheets("Customize").Range("C27").Value &
"'!Recalculate" with no luck. Thanks!

Code To Insert Cell Reference
 

Niek,
Thanks for taking the time to answer. I tried an exact copy of your code but
it did not work (syntax error). I also tried
Application.Run "'" & Sheets("Customize").Range("C27").Value &
"'!Recalculate" but it did not work either. Please advise.
Carl

"Niek Otten" wrote:

Application.Run Sheets("Customize").Range("C27").Value&"!Recalcula te"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Carl Bowman" wrote in message
...
The following line opens the macro "Recalculate" in the file
"Template.xls":
Application.Run "'Template.xls'!Recalculate"
The problem is the name of the file often changes. That change is recorded
in cell "C27" of worksheet "Customize". Can someone tell me the code to
have
the line reference this cell? I've tried many variations of:
Application.Run "'" & Sheets("Customize").Range("C27").Value &
"'!Recalculate" with no luck. Thanks!

Code To Insert Cell Reference
 

Should work if the hard coded version works. You are just building an
identical string to feed to the run command. there is no mystery here.

if
Application.Run "'Template.xls'!Recalculate"

works then you string

"'" & Sheets("Customize").Range("C27").Value & "'!Recalculate"

must produce "'Template.xls'!Recalculate"

And Template.xls must be open and hold the macro named Recalculate in a
general module, but that would be true for the hard coded example.
--
Regards,
Tom Ogilvy

"Carl Bowman" wrote in message
...
Niek,
Thanks for taking the time to answer. I tried an exact copy of your code
but
it did not work (syntax error). I also tried
Application.Run "'" & Sheets("Customize").Range("C27").Value &
"'!Recalculate" but it did not work either. Please advise.
Carl

"Niek Otten" wrote:

Application.Run Sheets("Customize").Range("C27").Value&"!Recalcula te"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Carl Bowman" wrote in message
...
The following line opens the macro "Recalculate" in the file
"Template.xls":
Application.Run "'Template.xls'!Recalculate"
The problem is the name of the file often changes. That change is
recorded
in cell "C27" of worksheet "Customize". Can someone tell me the code
to
have
the line reference this cell? I've tried many variations of:
Application.Run "'" & Sheets("Customize").Range("C27").Value &
"'!Recalculate" with no luck. Thanks!

Code To Insert Cell Reference
 

Niek,
I finally got the following to work:
Application.Run "'" & Sheets("Customize").Range("C17").Value & "'!Recalculate
I had to switch back to the correct workbook so it could find the worksheet
"Customize".
Thanks!

"Niek Otten" wrote:

Application.Run Sheets("Customize").Range("C27").Value&"!Recalcula te"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Carl Bowman" wrote in message
...
The following line opens the macro "Recalculate" in the file
"Template.xls":
Application.Run "'Template.xls'!Recalculate"
The problem is the name of the file often changes. That change is recorded
in cell "C27" of worksheet "Customize". Can someone tell me the code to
have
the line reference this cell? I've tried many variations of:
Application.Run "'" & Sheets("Customize").Range("C27").Value &
"'!Recalculate" with no luck. Thanks!

Code To Insert Cell Reference
 

I had to switch back to the correct workbook
No you don't. You just have to reference it. Assume it is in a workbook
name test.xls

Application.Run "'" & Worksheets("Test.xls").Sheets("Customize") _
.Range("C17").Value & "'!Recalculate"

Of couse if it was in cell C17 rather than C27, that would make a difference
as well.

--
Regards,
Tom Ogilvy

"Carl Bowman" wrote in message
...
Niek,
I finally got the following to work:
Application.Run "'" & Sheets("Customize").Range("C17").Value &
"'!Recalculate
I had to switch back to the correct workbook so it could find the
worksheet
"Customize".
Thanks!

"Niek Otten" wrote:

Application.Run Sheets("Customize").Range("C27").Value&"!Recalcula te"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Carl Bowman" wrote in message
...
The following line opens the macro "Recalculate" in the file
"Template.xls":
Application.Run "'Template.xls'!Recalculate"
The problem is the name of the file often changes. That change is
recorded
in cell "C27" of worksheet "Customize". Can someone tell me the code
to
have
the line reference this cell? I've tried many variations of:
Application.Run "'" & Sheets("Customize").Range("C27").Value &
"'!Recalculate" with no luck. Thanks!

Code To Insert Cell Reference
 

Your answer worked! Thanks again.

"Tom Ogilvy" wrote:

I had to switch back to the correct workbook
No you don't. You just have to reference it. Assume it is in a workbook
name test.xls

Application.Run "'" & Worksheets("Test.xls").Sheets("Customize") _
.Range("C17").Value & "'!Recalculate"

Of couse if it was in cell C17 rather than C27, that would make a difference
as well.

--
Regards,
Tom Ogilvy

"Carl Bowman" wrote in message
...
Niek,
I finally got the following to work:
Application.Run "'" & Sheets("Customize").Range("C17").Value &
"'!Recalculate
I had to switch back to the correct workbook so it could find the
worksheet
"Customize".
Thanks!

"Niek Otten" wrote:

Application.Run Sheets("Customize").Range("C27").Value&"!Recalcula te"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Carl Bowman" wrote in message
...
The following line opens the macro "Recalculate" in the file
"Template.xls":
Application.Run "'Template.xls'!Recalculate"
The problem is the name of the file often changes. That change is
recorded
in cell "C27" of worksheet "Customize". Can someone tell me the code
to
have
the line reference this cell? I've tried many variations of:
Application.Run "'" & Sheets("Customize").Range("C27").Value &
"'!Recalculate" with no luck. Thanks!