Correct workbook name

[pcsparky]

I'm trying to get this snippet of code to work (which updates a cell in the first workbook from the second workbook using the variable thisWbName instead of the actual name of of the workbook) but I don't know the correct way to get Excel to understand the variable thisWbName instead of the word.

Workbooks("~Dog Training Reports & Handler Information").Sheets("Dog and Handler Data").Range(cell).Offset(0, 5).Formula = "='[(thisWbName)]Overview'!$E$28"

Any ideas please.

[Andy Pope]

Current the variable is being treated as text because it is contained within the quotes. You need to break the syntax so it can recognise the variable.

Workbooks("~Dog Training Reports & Handler Information").Sheets("Dog and Handler Data").Range(cell).Offset(0, 5).Formula = _
                   "='[" & thisWbName &"]Overview'!$E$28"

[p45cal]

try
[vba]~~.Formula= "='[" & thisWbName & "]Sheet1'!$E$17"[/vba] if thisWbName is a string.
p45cal

ha.. beaten to it!

[pcsparky]

Thanks very much, both of you. Works a treat.