Formula Using A Variable in File & Pah

[Denali5283]

Hi There!

I have the following formula in my worksheet. I am simply trying to pull a cell value from one sheet to another. It works great until I try to replace some portions of the file name & path with variables.

ActiveCell.FormulaR1C1 = "='H:\End Of Month\08 - August\Percent of Biz\[180801_PctBiz_Totals]Sheet1'!R1C2"


I would like to replace "08 - August" in the path with a variable named "MonthFolderName" and "180801" in the file name with a variable named "MonthFileName".

I have spent hours researching how to do this with no results working.


Any help is appreciated! Thanks!

[Paul_Hossler]

Try




MsgBox
"='H:\End Of Month\" & MonthFolderName & "\Percent of Biz\[" & MonthFileName & "_PctBiz_Totals]Sheet1'!R1C2" ' <<<< comment out after testing

ActiveCell.FormulaR1C1 = "='H:\End Of Month\" & MonthFolderName & "\Percent of Biz\[" & MonthFileName & "_PctBiz_Totals]Sheet1'!R1C2"