Selected Answer
It's because of the way you refer to the worksheet.
Every tab has two names. One is the CodeName, the other what I like to call the TabName. The CodeName is the name by which VBA refers to the sheet, the TabName is the name that the user gives to the tab. The TabName is set by the user from the worksheet UI. The CodeName can only be set in the VB Editor properties.
When you look at the worksheet's properties in the VBE the very first property is called (Name), set in parentheses. That's the CodeName. Further down you see the Name property (without parentheses). That's the TabName. In the Project Explorer pane developers of VBA, in their infinite wisdom, reversed the use of parentheses. There you see the two names side by side, like Sheet1 (Sheet1), and the name set in parentheses is the TabName. It changes when you change the name on the tab and so does the Name property. Both names can be modified in the Properties pane, too.
The difference between the TabName and CodeName is that the former is a name, in string format, while the latter is an object variable representing the worksheet. Just like ActiveSheet always refers to the currently active sheet regardless of its name, Sheet1 always refers to "Sheet1", also regardless of its name. Pardon the play on words: If you change the TabName from "Sheet1" to "My Sheet", you could still refer to it by its CodeName Sheet1. Best, try this out in the VBE before you continue.
Of course, the syntax VBA has for handling an object or a string is completely different. The two can't be confused. A string can be used in place of an index to identify a member of a Collection. Take the WorkSheets or Sheets collection (the latter includes ChartSheets and PivotSheets). Sheets(1) refers to the Sheet identified by the first tab on the screen looking from the left. You can also refer to it by Name, like Worksheets("My Sheet"). If either sheet doesn't exist - by index or name - an error #13 will occur: Subscript out of range.
However, VBA already prepared an object variable for you that you can also use. Refer to the sheet simply as Sheet1, where Sheet1 is the CodeName. Therefore the following 3 ways of referencing cell A1 are interchangeable. (You can also refer to A1 as Range("A1") or Cells(1, "A"))
Worksheets(1).Cells(1, 1)
Worksheets("My Sheet").Cells(1, 1)
Sheet1.Cells(1, 1)
In your code, ReportTemplate.Range("B3:C200"), the third way is used. ReportTemplate is presented as a CodeName. There is no object variable by that name, no worksheet or sheet by that CodeName. Hence the the error #13.
Set the worksheet's CodeName to "ReportTemplate" or address the sheet by its TabName, Worksheets("ReportTemplate") and your VLookup function should work just fine. You can also declare your own obejct variable using the same name, like this:-
Dim ReportTemplate As Worksheet
Set ReportTemplate = Worksheets("ReportTemplate")
Observe that the string name "Report Template" is allowed a space but the object name ReportTemplate isn't.
BTW, when you use the With statement the items within its scope must be linked to it by a leading period, like this:-
With ZODIACS
TemplName = .Range("B2").Value
It's as if you write ZODIAC.Range("B2") but with the object removed to the With statement. The leading period must remain when the two parts are separated. I wonder if "Zodiac" is an existing CodeName. It might be this line that causes your error before it comes to the next. The missing period before "Range" will not cause a runtime error. It will just return B2 from the ActiveSheet instead of the one you intended.
