Copy/Paste Formulas Based on Number of Rows of Data in a different worksheet

[tebbdu]

I have a worksheet that has about 40 columns where each cell is calculating a formula for a financial model.
Each formula references data in a separate worksheet.
How do I create a macro that will only fill the 40 formula cells based on the number of rows of data I have in a separate worksheet?

Formula Worksheet - Header row plus 40 columns of formulas in each row
Data worksheet - raw data; number of columns fluctuates every day

I have seen solutions based on a single formula, but not based on a very large number of formulas like I have.

Can someone help me please?

 

[tebbdu]

Sorry - I did not describe the problem very well.

I have a worksheet with formulas in 40 different columns across each row.
Each of these rows corresponds to a row of raw data in a separate worksheet in the same workbook.

The number of rows of raw data fluctuates daily - and rather than just paste the formulas down about 10,000 rows just to ensure I cover all of the raw data - can I write a macro that will calculate how many rows are in the raw data sheet and then paste the 40 columns of formulas to the same number of rows?

 

[kevin lazell]

hello tebbdu
the following code will tell you how many rows of data there are i am assuming your raw data
is on sheet2

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = Sheets("sheet2").Range("a" & Rows.Count).End(xlUp).Row
y=1
range("a" & y & ":an" & x) 'your formula here

End Sub

should get you started
cheers
kevin