Problem with formula

[kluitna]

First off thanks to Al Chara, you code helped allot in getting me this far, but here is my problem

I can't get the follow code to work right:
strfile = Source
SheetName = Workbooks(Source).Worksheets(h).Name
ActiveCell.FormulaR1C1 = "='" & strfile & "'!R21C4"

it either puts the sheet numer in or just the file refering to the first work sheet. I can get one or the other but not both. gets frustrating to be so close to a goal.

Can anyone tell how to make this look at the file source and the sheet number coresspoding to h. any help is much apreciated.

Also at the end of the loop I want to close the sile souce, source is actually a varible. just a referece to a cell in the workbook. can't seem toget any of the functions to recognize it to close. I am a bit confused.

Again any help is much appreciated
Paul

 

[Damon Ostrander]

Hi kluitna,

I believe the problem is that the formula reference your code builds does not include the worksheet. It should be:

ActiveCell.FormulaR1C1 = "=[" & strfile & "]" & SheetName & "!R21C4"