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

J

Jmoz092

Board Regular
Joined
Sep 8, 2017
Messages
184
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
  2. 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.

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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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

J
  • Solved
Using LEFT Function On a Range Instead of A Single Cell Value
Replies
4
Views
127
Fluff
Fluff
K
  • Solved
Dynamic Sum Range Addresses...(?)
Replies
2
Views
508
Kenny_D
K
2
  • Question Question
Filter or Index based on cell value and column header
Replies
3
Views
253
Peter_SSs
Peter_SSs
S
  • Solved
Alternate to pivot table
Replies
5
Views
245
Fluff
Fluff
N
  • Solved
COUNTIF With Multiple Criteria From Different Ranges
Replies
3
Views
609
Alex Blakenburg
A

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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