Macro to Copy Formulas to Bottom Alongside a Query Table

[David Foran]

I have an ODBC Query that pulls GL activity from our ERP system into Excel. The size of this data increases the number of rows every month. In columns to the right of this data, I copy down some vlookup and other formulas that I use to organize the raw data and do sumifs on on other pages to create P&Ls and Balance Sheets etc.

I can run the model one month and everything works fine. Then the next month, it will not let me copy down the formulas past the previous months bottom row. So last month the query table say went from A2..G20,000 and my lookup formulas to the right were from H2..Z20,000. This month the guery table data goes from A2..G25,000 but my formulas to the right will not allow my to copy formulas below 20,000. With or without using the macro, Excel just refuses to let me paste into the area between H20,000 and H25,000

I think it has something to do with range names. Also, between last month and this month on did some Move/Copy tabs and had to click through the Yes dozens of times in order to add the pages.

Any thoughts?

 

[pbornemeier]

Define a dynamic named range for the formulas based on the number of rows present on your query page.
Then see if VBA can update the named range with the formula.