VBA to autofill range of cell, part of formula is concatenated string from referenced range

J

Jmoz092

Board Regular
Joined
Sep 8, 2017
Messages
182
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.

Code: 
[COLOR=#454545][FONT=&quot]Sub Change_1_Mos_Tab_Formulas()[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]'[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]' Change_1_Mos_Tab_Formulas Macro[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]' Changes the concatenated date in formulas on the 1 Mos summary sheet[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]'[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]
[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]' Column B[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Sheets("1 mos").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B16").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B17").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B18").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B19").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B20").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B21").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B22").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B23").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B24").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B25").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B26").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B27").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B28").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B29").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B30").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B31").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B32").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B33").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B34").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("B35").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ' Column C[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C16").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C17").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C18").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C19").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C20").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C21").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C22").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C23").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C24").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C25").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C26").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C27").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C28").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C29").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C30").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C31").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C32").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C33").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C34").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("C35").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ' Column D[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D16").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D17").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D18").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D19").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D20").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D21").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D22").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D23").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D24").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D25").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D26").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D27").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D28").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D29").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D30").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D31").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D32").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D33").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D34").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    Range("D35").Select[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    ActiveCell.FormulaR1C1 = _[/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]        "=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=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#454545][FONT=&quot]End Sub[/FONT][/COLOR]
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Another quandary here...

If we have a message box at workbook open ask for the current month and year, and that data gets input and concatenated into cells on the Reference worksheet, how can I use VBA to automatically recognize the next correct 6 months worth of concatenated values?

i.e., if the user enters November, 2017 into the workbook open message box, then the month and year in the Reference worksheet cells will concatenate into "112017". That number will be used in the formulas on the 1 Mos worksheet. The 2 Mos worksheet would need to use "122017" , then the 3 Mos worksheet would need to know that the month could not be 13, but rather 1 and the year would need to change accordingly also. Etc, etc

Can this be achieved with VBA or worksheet formulas??
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
COUNTIF With Multiple Criteria From Different Ranges
Replies
3
Views
141
Alex Blakenburg
A
K
  • Question
conditional formatting question
Replies
2
Views
163
kerm007
K
C
  • Question
VBA: Filter specific data from table then make formula and put result to summary workbook
Replies
1
Views
391
HighAndWilder
H
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
334
pure vito
P
gtd526
  • Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
131
gtd526
gtd526

Forum statistics

Threads
1,215,125
Messages
6,123,195
Members
449,090
Latest member
bes000

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top