Changing from formulas to values

[kasbac]

Hi there

I have previously gotten some help writing this code which idetifies the last column in a sheet and copy's the formulas found in it to the following coulmn.

    Sub test()
    Sheets("Overview Q2 2011 (2)").Select
    LastCol = Range("B2").End(xlToRight).Column
    Cells(1, LastCol).EntireColumn.Copy Cells(1, LastCol + 1)
    
    End Sub

I would very much like that in addition to this the macro would after it have transfered the formulas, fix the numbers that are found in the coulmn from which the formula comes.

An example lets say that the formulas are transfered from coulmn B to coulmn C. After the transfer is done I would like for the values in column B to be replaced with values instead of the formulas in the coulmn. Hope that makes sense?

If any clarification is needed please let me know

 

[VoG]

Try

Sub test()
Sheets("Overview Q2 2011 (2)").Select
LastCol = Range("B2").End(xlToRight).Column
With Cells(1, LastCol).EntireColumn
    .Copy Cells(1, LastCol + 1)
    .Value = .Value
End With
End Sub

 

[Richard Schollar]

Hi

Sub test()
    With Sheets("Overview Q2 2011 (2)")
       LastCol = .Range("B2").End(xlToRight).Column
       .Cells(1, LastCol).EntireColumn.Copy .Cells(1, LastCol + 1)
       .Cells(1, LastCol).EntireColumn.Value = .Cells(1, LastCol).EntireColumn..Value
   End With
    
    End Sub

 

[kasbac]

worked like a charm both of them thank you!