Naming an Excel Range for the entire active sheet

Searched high and low and can't quite get this right.

Looking for a macro that names all cells as "Table9" --> But I want it as a range, not as a table.
The number of rows will change day over day, but the columns are fixed at 20.

Thank you in advance.

You don't need VBA for this - use a dynamic named range instead.

Go to the Name Manager, add a new name ('Table9') and in the 'Refers To' field add: =OFFSET($A$1,0,0,COUNTA($A:$A),20)

That will adjust automatically as data is added to Col A.

This is like 99% there. The purpose of this is I'm bringing in an external worksheet everyday, so the sheet name changes each day - and it's really unpredictable.

So where you have 'Set ws = Worksheets("Sheet1") <--I need that Sheet1 just to be the ActiveSheet... Is that possible?

Worked like a charm - Thank you all!