Hi all
I have created a macro to retrieve data from selected sheets to a summary page in the same workbook. The code is fine but took some time to put together. I'm wondering if is there is a simpler solution. The patterns are easy to follow. Thanks for your help
Mark
I have created a macro to retrieve data from selected sheets to a summary page in the same workbook. The code is fine but took some time to put together. I'm wondering if is there is a simpler solution. The patterns are easy to follow. Thanks for your help
Mark
Code:
Sub SetFormulas()
Const s = "Sheet1"
With Sheets(s)
.Range("E5") = Sheets(3).Range("G3").Value 'no. of FuncLocs
.Range("E6") = Sheets(4).Range("G3").Value
.Range("E7") = Sheets(5).Range("G3").Value
.Range("E8") = Sheets(6).Range("G3").Value
.Range("E9") = Sheets(7).Range("G3").Value
.Range("E10") = Sheets(8).Range("G3").Value
.Range("E11") = Sheets(9).Range("G3").Value
.Range("E12") = Sheets(10).Range("G3").Value
.Range("E13") = Sheets(11).Range("G3").Value
.Range("E14") = Sheets(12).Range("G3").Value
.Range("E15") = Sheets(13).Range("G3").Value
.Range("E16") = Sheets(14).Range("G3").Value
.Range("E17") = Sheets(15).Range("G3").Value
.Range("E18") = Sheets(16).Range("G3").Value
.Range("E19") = Sheets(17).Range("G3").Value
.Range("E20") = Sheets(18).Range("G3").Value
.Range("F5").Formula = "='3'!$J$3" ''Total no. of BOMs
.Range("F6").Formula = "='4'!$J$3"
.Range("F7").Formula = "='5'!$J$3"
.Range("F8").Formula = "='6'!$J$3"
.Range("F9").Formula = "='7'!$J$3"
.Range("F10").Formula = "='8'!$J$3"
.Range("F11").Formula = "='9'!$J$3"
.Range("F12").Formula = "='10'!$J$3"
.Range("F13").Formula = "='11'!$J$3"
.Range("F14").Formula = "='12'!$J$3"
.Range("F15").Formula = "='13'!$J$3"
.Range("F16").Formula = "='14'!$J$3"
.Range("F17").Formula = "='15'!$J$3"
.Range("F18").Formula = "='16'!$J$3"
.Range("F19").Formula = "='17'!$J$3"
.Range("F20").Formula = "='18'!$J$3"