Excel: Code works when 2 cells are populated, but doesn't work when they are empty. I get Run-Time Error 1004

Hello All, The code below is for 12 cells only (all other cells in row 6 are locked), one for each month in row 6. The "GETPIVOTDATA" code will populate the each month below. Jan data will go in the C6 cell Feb data will go in the D6 cell Mar data will go in the E6 cell, etc.... The issue is that when there is no data in January (C6) or February (D6), the code below gives me an error. The error I get is "Run-Time Error 1004". It is also worthy to note that in row 6, all cells are locked from editing except cells C6 through N6. Again, the code works perfectly if there is already data in BOTH the C6 AND D6 cells. I would like this to work even if one or both of them are blank. Can anyone make this happen? Thanks much!

ActiveCell.End(xlToRight).Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""REPORT"",'Customer Chart'!R1C1,""NAME"",""John"")"

This question generated 14 answers. To proceed to the answers, click here.

This thread is current as of March 03, 2017.

For more resources for Microsoft Excel