VBA Index Match doesn't allow spaces?

[excelnoob001]

Context: I am trying to automate a task through a macro. I have a source file and a destination file and dumping in specific data from the source file onto the destination file. However, since the source file has a different table layout, I created a hidden sheet in the destination file to hold this data, then transfer it to my actual sheets.

The problem is that index match is not recognizing the name of worksheets that have spaces inside of them.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sheets("Richmond Hill").Range("C7").Select
ActiveCell.FormulaArray = _
"=INDEX(AllGridTemp!$C$3:$C$48, MATCH(Richmond Hill!A7&Richmond Hill!B7,AllGridTemp!$A$3:$A$48&AllGridTemp!$B$3:$B$48,0))"
              Set SourceRange = Worksheets("Richmond Hill").Range("C7")
              Set fillRange = Worksheets("Richmond Hill").Range("C7:C52")
              SourceRange.AutoFill Destination:=fillRange

When I run this, I get a #NAME ? error.

Also, when I try to manually enter the formula, I get a dialog box to select a file update values.

How do I make sure the formula accounts for spaces in sheet name?

Thank you.
[/FONT]

 

[Joe4]

You need to enclose the sheet names in single quotes, i.e.
'Richmond Hill'!A7