Optimizing Coding Structure and Workbook Speed/Functionality

[reberryjr]

I need some guidance on how to best approach a situation. I have a workbook that will have 40+ sheets. One of the sheets is mean to serve as a Summary sheet, containing data from the others. In creating this summary sheet, I enter a slew of formulas (from column F through column BV). This seems to be causing my workbook to drag. I've googled the situation and I see suggestions that I should limit the use of formulas, and use code instead. I'm struggling with how I would effectively translate these formulas (I've included a small example below) to code.

Immediate challenges include:

• Not ever worksheet exists until it's created by a command button on a userform, hence the Indirect functions.
• I would want the formula results to update as information on the respective sheet is changed, as well as every day, since many of these are date calculations, based off of =Today().
• I'm still learning VBA, so I don't have extensive knowledge of its capabilities.

'ws2.Range("F" & LastRow2 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-2]&""'!D:D"")='Client Summaries'!RC[-2],INDIRECT(""'""&RC[-2]&""'!L:L"")))"
'ws2.Range("G" & LastRow2 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-3]&""'!D:D"")='Client Summaries'!RC[-3],INDIRECT(""'""&RC[-3]&""'!R:R"")))"
'ws2.Range("H" & LastRow2 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-4]&""'!D:D"")='Client Summaries'!RC[-4],INDIRECT(""'""&RC[-4]&""'!AA:AA"")))"
'ws2.Range("I" & LastRow2 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-5]&""'!D:D"")='Client Summaries'!RC[-5],INDIRECT(""'""&RC[-5]&""'!AK:AK"")))"
'ws2.Range("J" & LastRow2 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-6]&""'!D:D"")='Client Summaries'!RC[-6],INDIRECT(""'""&RC[-6]&""'!AU:AU"")))"
'ws2.Range("K" & LastRow2 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-7]&""'!D:D"")='Client Summaries'!RC[-7],INDIRECT(""'""&RC[-7]&""'!BE:BE"")))"
'ws2.Range("L" & LastRow2 + 1).FormulaArray = "=MAX(IF(INDIRECT(""'""&RC[-8]&""'!D:D"")='Client Summaries'!RC[-8],INDIRECT(""'""&RC[-8]&""'!BO:BO"")))"
'ws2.Range("M" & LastRow2 + 1).FormulaArray = "=MAX(IF(Stats!C[-10]='Client Summaries'!RC[-10],Stats!C[-5]))"
'ws2.Range("N" & LastRow2 + 1).FormulaArray = "=RC[-13]-RC[-1]"
'ws2.Range("O" & LastRow2 + 1).FormulaArray = "=INDEX(STATS!C[-5],MATCH(MIN(IF(STATS!C[-12]=RC[-12],STATS!C[-7])),IF(STATS!C[-12]=RC[-12],STATS!C[-7]),0))"
'ws2.Range("P" & LastRow2 + 1).FormulaArray = "=MIN(IF(STATS!C[-13]='CLIENT SUMMARIES'!RC[-13],STATS!C[-8]))"
'ws2.Range("Q" & LastRow2 + 1).Value = "=RC[-1]-RC[-2]"
'ws2.Range("R" & LastRow2 + 1).FormulaArray = "=INDEX(STATS!C[-8],MATCH(MAX(IF(STATS!C[-15]=RC[-15],STATS!C[-10])),IF(STATS!C[-15]=RC[-15],STATS!C[-10]),0))"
'ws2.Range("S" & LastRow2 + 1).Value = "=RC[-1]-RC[-4]"
'ws2.Range("T" & LastRow2 + 1).FormulaArray = "=INDEX(STATS!C[-8],MATCH(MIN(IF(STATS!C[-17]=RC[-17],STATS!C[-12])),IF(STATS!C[-17]=RC[-17],STATS!C[-12]),0))"
'ws2.Range("U" & LastRow2 + 1).FormulaArray = "=MIN(IF(STATS!C[-18]='CLIENT SUMMARIES'!RC[-18],STATS!C[-9]))"
'ws2.Range("V" & LastRow2 + 1).Value = "=RC[-1]-RC[-2]"