Jmoz092
Board Regular
- Joined
- Sep 8, 2017
- Messages
- 184
- Office Version
- 365
- 2011
- Platform
- Windows
- MacOS
I have a workbook that I use each month, each year and I have to do a number of different things to it in order to ready it for the next month's use. I'm trying to get the workbook to take care of itself, so to speak. This is one of the steps I'm trying to automate.
I have 6 tabs within the workbook that represent months (1 mos, 2 mos, 3 mos, 4 mos, 5 mos, 6 mos). In columns B, C, and D I have a formula that contains a concatenation of the current month and year. This value (mmyyyy) can be referenced by a cell within the workbook (separate from the mos tabs).
How can I insert that concatenated value into the existing formulas in B16:D35 of the 1 mos, 2 mos, 3 mos, 4 mos, 5 mos, and 6 mos tabs in my workbook now, and also in the future?
Here is the current "macro" I have. I created it from running the recorder. In the past year, I've just been going through this and changing the concatenated value by hand and then running the macro. Ick ;( Obviously, I'd like to quicken the process going forward so that I can pass this project off to excel users that are more novice than myself.
The concatenated value is "102017" in the code below. I've created a cell to reference this value, going forward, on a tab named "Reference" in cell A10.
I have 6 tabs within the workbook that represent months (1 mos, 2 mos, 3 mos, 4 mos, 5 mos, 6 mos). In columns B, C, and D I have a formula that contains a concatenation of the current month and year. This value (mmyyyy) can be referenced by a cell within the workbook (separate from the mos tabs).
How can I insert that concatenated value into the existing formulas in B16:D35 of the 1 mos, 2 mos, 3 mos, 4 mos, 5 mos, and 6 mos tabs in my workbook now, and also in the future?
Here is the current "macro" I have. I created it from running the recorder. In the past year, I've just been going through this and changing the concatenated value by hand and then running the macro. Ick ;( Obviously, I'd like to quicken the process going forward so that I can pass this project off to excel users that are more novice than myself.
The concatenated value is "102017" in the code below. I've created a cell to reference this value, going forward, on a tab named "Reference" in cell A10.
Code:
[COLOR=#454545][FONT="]Sub Change_1_Mos_Tab_Formulas()[/FONT][/COLOR]
[COLOR=#454545][FONT="]'[/FONT][/COLOR]
[COLOR=#454545][FONT="]' Change_1_Mos_Tab_Formulas Macro[/FONT][/COLOR]
[COLOR=#454545][FONT="]' Changes the concatenated date in formulas on the 1 Mos summary sheet[/FONT][/COLOR]
[COLOR=#454545][FONT="]'[/FONT][/COLOR]
[COLOR=#454545][FONT="]
[/FONT][/COLOR]
[COLOR=#454545][FONT="]' Column B[/FONT][/COLOR]
[COLOR=#454545][FONT="] Sheets("1 mos").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B16").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710001'!R[21]C[6]:'Data 201710001'!R[26]C[6], 102017, 'Data 201710001'!R[21]C[7]:'Data 201710001'!R[26]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B17").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710002'!R[20]C[6]:'Data 201710002'!R[25]C[6], 102017, 'Data 201710002'!R[20]C[7]:'Data 201710002'!R[25]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B18").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710003'!R[19]C[6]:'Data 201710003'!R[24]C[6], 102017, 'Data 201710003'!R[19]C[7]:'Data 201710003'!R[24]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B19").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710004'!R[18]C[6]:'Data 201710004'!R[23]C[6], 102017, 'Data 201710004'!R[18]C[7]:'Data 201710004'!R[23]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B20").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710005'!R[17]C[6]:'Data 201710005'!R[22]C[6], 102017, 'Data 201710005'!R[17]C[7]:'Data 201710005'!R[22]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B21").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710006'!R[16]C[6]:'Data 201710006'!R[21]C[6], 102017, 'Data 201710006'!R[16]C[7]:'Data 201710006'!R[21]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B22").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710007'!R[15]C[6]:'Data 201710007'!R[20]C[6], 102017, 'Data 201710007'!R[15]C[7]:'Data 201710007'!R[20]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B23").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710008'!R[14]C[6]:'Data 201710008'!R[19]C[6], 102017, 'Data 201710008'!R[14]C[7]:'Data 201710008'!R[19]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B24").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710009'!R[13]C[6]:'Data 201710009'!R[18]C[6], 102017, 'Data 201710009'!R[13]C[7]:'Data 201710009'!R[18]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B25").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710010'!R[12]C[6]:'Data 201710010'!R[17]C[6], 102017, 'Data 201710010'!R[12]C[7]:'Data 201710010'!R[17]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B26").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710011'!R[11]C[6]:'Data 201710011'!R[16]C[6], 102017, 'Data 201710011'!R[11]C[7]:'Data 201710011'!R[16]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B27").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710012'!R[10]C[6]:'Data 201710012'!R[15]C[6], 102017, 'Data 201710012'!R[10]C[7]:'Data 201710012'!R[15]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B28").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710013'!R[9]C[6]:'Data 201710013'!R[14]C[6], 102017, 'Data 201710013'!R[9]C[7]:'Data 201710013'!R[14]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B29").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710014'!R[8]C[6]:'Data 201710014'!R[13]C[6], 102017, 'Data 201710014'!R[8]C[7]:'Data 201710014'!R[13]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B30").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710015'!R[7]C[6]:'Data 201710015'!R[12]C[6], 102017, 'Data 201710015'!R[7]C[7]:'Data 201710015'!R[12]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B31").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710016'!R[6]C[6]:'Data 201710016'!R[11]C[6], 102017, 'Data 201710016'!R[6]C[7]:'Data 201710016'!R[11]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B32").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710017'!R[5]C[6]:'Data 201710017'!R[10]C[6], 102017, 'Data 201710017'!R[5]C[7]:'Data 201710017'!R[10]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B33").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710018'!R[4]C[6]:'Data 201710018'!R[9]C[6], 102017, 'Data 201710018'!R[4]C[7]:'Data 201710018'!R[9]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B34").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710019'!R[3]C[6]:'Data 201710019'!R[8]C[6], 102017, 'Data 201710019'!R[3]C[7]:'Data 201710019'!R[8]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("B35").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710020'!R[2]C[6]:'Data 201710020'!R[7]C[6], 102017, 'Data 201710020'!R[2]C[7]:'Data 201710020'!R[7]C[7])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] ' Column C[/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C16").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710001'!R[27]C[5]:'Data 201710001'!R[32]C[5], 102017, 'Data 201710001'!R[27]C[6]:'Data 201710001'!R[32]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C17").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710002'!R[26]C[5]:'Data 201710002'!R[31]C[5], 102017, 'Data 201710002'!R[26]C[6]:'Data 201710002'!R[31]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C18").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710003'!R[25]C[5]:'Data 201710003'!R[30]C[5], 102017, 'Data 201710003'!R[25]C[6]:'Data 201710003'!R[30]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C19").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710004'!R[24]C[5]:'Data 201710004'!R[29]C[5], 102017, 'Data 201710004'!R[24]C[6]:'Data 201710004'!R[29]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C20").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710005'!R[23]C[5]:'Data 201710005'!R[28]C[5], 102017, 'Data 201710005'!R[23]C[6]:'Data 201710005'!R[28]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C21").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710006'!R[22]C[5]:'Data 201710006'!R[27]C[5], 102017, 'Data 201710006'!R[22]C[6]:'Data 201710006'!R[27]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C22").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710007'!R[21]C[5]:'Data 201710007'!R[26]C[5], 102017, 'Data 201710007'!R[21]C[6]:'Data 201710007'!R[26]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C23").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710008'!R[20]C[5]:'Data 201710008'!R[25]C[5], 102017, 'Data 201710008'!R[20]C[6]:'Data 201710008'!R[25]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C24").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710009'!R[19]C[5]:'Data 201710009'!R[24]C[5], 102017, 'Data 201710009'!R[19]C[6]:'Data 201710009'!R[24]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C25").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710010'!R[18]C[5]:'Data 201710010'!R[23]C[5], 102017, 'Data 201710010'!R[18]C[6]:'Data 201710010'!R[23]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C26").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710011'!R[17]C[5]:'Data 201710011'!R[22]C[5], 102017, 'Data 201710011'!R[17]C[6]:'Data 201710011'!R[22]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C27").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710012'!R[16]C[5]:'Data 201710012'!R[21]C[5], 102017, 'Data 201710012'!R[16]C[6]:'Data 201710012'!R[21]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C28").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710013'!R[15]C[5]:'Data 201710013'!R[20]C[5], 102017, 'Data 201710013'!R[15]C[6]:'Data 201710013'!R[20]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C29").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710014'!R[14]C[5]:'Data 201710014'!R[19]C[5], 102017, 'Data 201710014'!R[14]C[6]:'Data 201710014'!R[19]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C30").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710015'!R[13]C[5]:'Data 201710015'!R[18]C[5], 102017, 'Data 201710015'!R[13]C[6]:'Data 201710015'!R[18]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C31").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710016'!R[12]C[5]:'Data 201710016'!R[17]C[5], 102017, 'Data 201710016'!R[12]C[6]:'Data 201710016'!R[17]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C32").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710017'!R[11]C[5]:'Data 201710017'!R[16]C[5], 102017, 'Data 201710017'!R[11]C[6]:'Data 201710017'!R[16]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C33").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710018'!R[10]C[5]:'Data 201710018'!R[15]C[5], 102017, 'Data 201710018'!R[10]C[6]:'Data 201710018'!R[15]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C34").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710019'!R[9]C[5]:'Data 201710019'!R[14]C[5], 102017, 'Data 201710019'!R[9]C[6]:'Data 201710019'!R[14]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("C35").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710020'!R[8]C[5]:'Data 201710020'!R[13]C[5], 102017, 'Data 201710020'!R[8]C[6]:'Data 201710020'!R[13]C[6])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] ' Column D[/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D16").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710001'!R[33]C[4]:'Data 201710001'!R[52]C[4], 102017, 'Data 201710001'!R[33]C[5]:'Data 201710001'!R[52]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D17").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710002'!R[32]C[4]:'Data 201710002'!R[51]C[4], 102017, 'Data 201710002'!R[32]C[5]:'Data 201710002'!R[51]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D18").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710003'!R[31]C[4]:'Data 201710003'!R[50]C[4], 102017, 'Data 201710003'!R[31]C[5]:'Data 201710003'!R[50]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D19").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710004'!R[30]C[4]:'Data 201710004'!R[49]C[4], 102017, 'Data 201710004'!R[30]C[5]:'Data 201710004'!R[49]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D20").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710005'!R[29]C[4]:'Data 201710005'!R[48]C[4], 102017, 'Data 201710005'!R[29]C[5]:'Data 201710005'!R[48]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D21").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710006'!R[28]C[4]:'Data 201710006'!R[47]C[4], 102017, 'Data 201710006'!R[28]C[5]:'Data 201710006'!R[47]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D22").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710007'!R[27]C[4]:'Data 201710007'!R[46]C[4], 102017, 'Data 201710007'!R[27]C[5]:'Data 201710007'!R[46]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D23").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710008'!R[26]C[4]:'Data 201710008'!R[45]C[4], 102017, 'Data 201710008'!R[26]C[5]:'Data 201710008'!R[45]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D24").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710009'!R[25]C[4]:'Data 201710009'!R[44]C[4], 102017, 'Data 201710009'!R[25]C[5]:'Data 201710009'!R[44]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D25").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710010'!R[24]C[4]:'Data 201710010'!R[43]C[4], 102017, 'Data 201710010'!R[24]C[5]:'Data 201710010'!R[43]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D26").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710011'!R[23]C[4]:'Data 201710011'!R[42]C[4], 102017, 'Data 201710011'!R[23]C[5]:'Data 201710011'!R[42]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D27").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710012'!R[22]C[4]:'Data 201710012'!R[41]C[4], 102017, 'Data 201710012'!R[22]C[5]:'Data 201710012'!R[41]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D28").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710013'!R[21]C[4]:'Data 201710013'!R[40]C[4], 102017, 'Data 201710013'!R[21]C[5]:'Data 201710013'!R[40]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D29").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710014'!R[20]C[4]:'Data 201710014'!R[39]C[4], 102017, 'Data 201710014'!R[20]C[5]:'Data 201710014'!R[39]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D30").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710015'!R[19]C[4]:'Data 201710015'!R[38]C[4], 102017, 'Data 201710015'!R[19]C[5]:'Data 201710015'!R[38]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D31").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710016'!R[18]C[4]:'Data 201710016'!R[37]C[4], 102017, 'Data 201710016'!R[18]C[5]:'Data 201710016'!R[37]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D32").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710017'!R[17]C[4]:'Data 201710017'!R[36]C[4], 102017, 'Data 201710017'!R[17]C[5]:'Data 201710017'!R[36]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D33").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710018'!R[16]C[4]:'Data 201710018'!R[35]C[4], 102017, 'Data 201710018'!R[16]C[5]:'Data 201710018'!R[35]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D34").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710019'!R[15]C[4]:'Data 201710019'!R[34]C[4], 102017, 'Data 201710019'!R[15]C[5]:'Data 201710019'!R[34]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] Range("D35").Select[/FONT][/COLOR]
[COLOR=#454545][FONT="] ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT="] "=SUMIF('Data 201710020'!R[14]C[4]:'Data 201710020'!R[33]C[4], 102017, 'Data 201710020'!R[14]C[5]:'Data 201710020'!R[33]C[5])"[/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="] [/FONT][/COLOR]
[COLOR=#454545][FONT="]End Sub[/FONT][/COLOR]