I have a spreadsheet with about 14,000-17000 rows and 9 columns that i have to have a vlookup happening in. It pulls data from a seperate sheet.

The problem is that process takes about 10-40minutes depending on the computer it is run on, and my job is to make those vlookups happen in each column, then paste special values to get the actual values in the cells. Right now i have the vlookup (the formula works) in row 5 column X (have 9 columns to run these on) and i find the final row, and i have to copy row5 columnX and paste from row6 colX to rowFinal colX.

So i figured the best plan of attack would be to do a .FillDown and then after it did that, copy the selection and do a Selection.PasteSpecial xlValues and that would be good. The only problem is that it doesnt want to calculate the values before i copy and paste special, so i only get empty cells when they should all be full.

My question is this
How can I get my script to pause till the vlookups are done, then copy and paste special

Thank
Cory