Collecting all relevant data from xlsm files
It's been a while since I wrote VBA, but I seem to remember Application.Calculation = xlCalculationManual often sped up my programs considerably. This prevents formulae from being evaluated while you're updating cell values, and can be reversed via Application.Calculation = xlCalculationAutomatic.