The apostrophe in this formula comments out the rest of the line:
ActiveCell.FormulaR1C1 = _
"=INDIRECT(CONCATENATE(" '[filename.xls]",$A$2,"'!$AQ$466"))"
I've tried putting it in double quotes,
"=INDIRECT(CONCATENATE(" "'"[MonthlyTracking.xls]",$A$2,""'"!$AQ$466"))"
but it gives the error "expected end of statement"
Why are you using the concatenate function?
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
I'm accessing a sheet in a different file based on the sheet name (month by month it changes) so in march I want to access the march sheet, but in april I want to access the april sheet
$A$2 contains the sheet name
The sheetname contained in A2 belongs to which file? The monthly tracking file?
yes, the monthly tracking file
Oops i missed out another element - what is the sheetname which contains A2?
SEMreport.xls
How can it be having an extension of .xls? Is the tab named SEMreport.xls?
you need to double all the quotes inside the formula
ActiveCell.FormulaR1C1 = _ "=INDIRECT(CONCATENATE("" '[filename.xls]"",$A$2,""'!$AQ$466""))"
Josie
if at first you don't succeed try doing it the way your wife told you to
Thanks for your help Arlu1201 and JosephP
Joseph, your solution worked. Thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks