Copy Special Paste in the same column

[iwilli]

I'm baaacckk. In the processes of cleaning up my macro recorded code I have run into a problem. I have the formula in column G. I need to have just the values after the formula has been calculated but in the same column. Here is my code. What am I missing?

    Range("G2:G" & (Range("A" & Rows.Count).End(xlUp).Row)).FormulaR1C1 = _
    "=VLOOKUP(RC[-6],[VLookup_2012.xlsx]Condensed_Weekly_Advisor_Result!C1:C7,7,FALSE)"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[Smitty]

Welcome to the Board!

Try this (not tested):

    With Range("G2:G" & (Range("A" & Rows.Count).End(xlUp).Row))
        .FormulaR1C1 = _
            "=VLOOKUP(RC[-6],[VLookup_2012.xlsx]Condensed_Weekly_Advisor_Result!C1:C7,7,FALSE)"
        Application.Calculate
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With

Your code isn't selecting anything, so what's being copied/pasted is whatever is actually selected at the time.

HTH,

 

[iwilli]

It WORKED! Thanks. That is awesome!