Resize range (table) in VBA based on last row value in another worksheet

Hi
I have a worksheet where data is pasted from an external source.
I then have a second worksheet which is a formatted table with various formulas in each cell that converts the data from the worksheet where the data is pasted.

I want to create a macro that resizes the table/range in worksheet 2 to the same number of rows in the first worksheet. The purpose here is to extend the formulas up or down in the formula table depending on the row count of the first worksheet.

I'm open to suggestions here, but what I've tried so far is putting the

My first macro finds the last row in the worksheet and pastes it in a field.

Please Login or Register  to view this content.

I was hoping to then use this cell to reference the row count in my resize table macro. i.e instead of the range reference (Range("$A$1:$X$2985")).

Please Login or Register  to view this content.

Any thoughts?