Formula References Other Sheet But Errors

.
.
VBA macro that creates a formula is creating an error : # NAME

Here is the macro :

Please Login or Register  to view this content.

And this is the formula created by the macro. Note that the sheet names will have empty spaces. Removing the empty spaces is not an option.

B4 is the error ... B6 is the formula created and inserted into B4.

Note the single quotation marks inserted in the formula ----- they should not be there. How do I get rid of them ?


Error.jpg