Copy Formulas and Paste into the Last Set # of Rows Only & Paste Values

[Mr. Ice]

Hey guys,

I'm working on a macro that would allow me to copy formulas from one row with dozens of columns and paste into a range. The issue is that the number of rows in the range is massive. The formulas are pulling from a table that includes data from the start of the year onwards. The macro will be run weekly to get the new data set and update the table.

To shorten the execution time, I'm wanting to do the copy/paste formula in just the last 1000 rows of the table, and then paste as values. How can I go about writing a macro that copies the formula from the top row and pastes it into the last 1000 rows of the weekly updated table?

I'm attaching a worksheet with a representation of what I'm trying to achieve. For this example, lets say I'm trying to update the last 5 rows only on a weekly basis (assumption being that I will never have more than 5 new rows of data to add).

The code I have so far that just updates the entire table:

Please Login or Register  to view this content.

Book1.xlsx

[Mr. Ice]

Anyone????