I've got code to rename a sheet based on cell contents:

    Dim sheetName1 As String
    sheetName1 = Sheets("OpenFiles").Range("D2").Value & " Adv Summary"
    Sheets("RemoveDuplicates").Name = sheetName1
And it works beautifully 'most' of the time, when acting directly on the sheet, like this:

    Sheets(sheetName1).Range("B2").FormulaR1C1 = "=VLOOKUP(RC[-1],'Rev Report'!C:C[10],11,FALSE)"
    Sheets(sheetName1).Range("C2").FormulaR1C1 = "=VLOOKUP(RC[-2],'Rev Report'!C[-1]:C,2,FALSE)"
    Sheets(sheetName1).Range("D2").FormulaR1C1 = "=SUMIF('Rev Report'!C[-2],RC[-3],'Rev Report'!C[9])"
It doesn't work, however, when used during formula creation... see code below. I get a couple of "Update Values: sheetName1" during execution. Doesn't keep the macro from finishing, but those formulae don't work properly.

    Sheets("UD Aired").Range("H2").FormulaR1C1 = _
        "=IF(ISERROR(RC[-2]*VLOOKUP(RC[-7],'sheetName1'!R28C1:R12000C6,6,FALSE)),0,RC[-2]*VLOOKUP(RC[-7],'sheetName1'!R28C1:R12000C6,6,FALSE))"
Any help and/or direction is greatly appreciated!