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.

Sub LastRow1()

'insert last row onto macro sheet cell A20

Dim c           As Object

    With Sheets("CopyPasteApM").Range("A:A")
     Set c = .Find(What:="*", LookIn:=xlValues, Lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious)
    End With
 

Sheets("Macros").Range("A20").Value = c.Row


End Sub
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")).

Sub ResizeTable2()

' Resize table on formatdata sheet
 
    Sheets("FormatData").Select
    
    ActiveSheet.ListObjects("Table1").Resize Range("$A$1:$X$2985")
    Sheets("Macros").Range(.Range("A20")).Select
    
End Sub
Any thoughts?