| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| INSERT OBJECT BASED ON CELL REFERENCE | Excel Worksheet Functions | |||
| Can I insert a cell reference into a block of text? | Excel Discussion (Misc queries) | |||
| How can I insert a cell reference in a footer (eg for variable foo | Excel Discussion (Misc queries) | |||
| Need code to insert cell value | Excel Programming | |||
| need code to insert cell value | Excel Programming | |||
Hybrid Mode
