I'm apparently missing the proper syntax or method of using a string in
formulas that the VB code pastes in one spreadsheet
([WorkBook2.xls]CombList'!$E7 through $E25 one at a time) referencing values
in the corresponding cell location in another spreadsheet
(WorkBook1.xls]CombList'!$E7 through $E25 one at a time).

Each workday a new WorkBook is created (e.g. WorkBook2.xls) that has
formulas comparing E7 in today's WorkBook to the same cells on the prior
workday's values (e.g. WorkBook1.xls). For example, I want to use VB to
paste a formula in F7 showing the prior workday's value from E7 (e.g. a
formula that sets ([WorkBook2.xls]CombList'!$F7 equal to
WorkBook1.xls]CombList'!$E7). The filenames and folder locations can vary
depending on not only the workday but also which project is being tracked.

My code excerpt is shown below.
It bombs off at the statement: Range("F7").Formula = FormulaString

Any help for this novice VB programmer?


Dim LinkFN As String
Dim FormulaString As String

' Get path and filename to the file that you want to compare this Hit List
to
LinkFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls", Title:="Please select the Excel file that you want to compare today's
List against.")
If LinkFN = "False" Then
MsgBox "No file selected. You must select a file"
Exit Sub
End If

FormulaString = "=" & LinkFN & "CombList'!E7"
Range("F7").Formula = FormulaString