I have a workbook that had links to about 80 other workbooks. It was really slow to load up. I decided to stop using links to speed it up. This meant finding a way to use VBA sumproduct to carry out some of the calculations. I have a headache now since I am getting #Value! error from my VBA code.
option explicit Sub VBA_Sumproduct Dim rng_1 as range Dim op_cell as range Dim Model as String Dim formula as string Dim wb as workbook dim wb2 as workbook set wb2 = workbooks("wb2.xlsm") 'workbooks.open("Filepath") set wb = workbooks("wb1.xlsm") set rng_1=wb.sheets("A1").range("E2:E500") set op_cell = wb2.sheets("T1").range("E20") model = "Complete" Formula = "sumproduct((rng_1="" & model & "")" op_cell = application.evaluate(Formula) wb.save wb.close end sub