Mental block - how to set a range with sheets and cells

I'm at a mental block. This is inside a private sub that calls it and provides the EndRow argument. I it works, but only if I first "select" the sheet on which I want the formula placed:

Please Login or Register  to view this content.

What I'd like to do is add the sheetname to the set statement so I can avoid selecting or activating the sheet. Something like this:

Please Login or Register  to view this content.

I know that's incorrect, but I'm at a mental block on it.

Presumably

Formula:

Please Login or Register  to view this content.

or better still

Formula:

Please Login or Register  to view this content.

even better use the Sheet VBA code name rather than the sheet tab name since the tab name is too easily changed and will screw up your macro.

Formula:

Please Login or Register  to view this content.