Copy formulaes to next column and make the previous data static(remove the formulaes)

[deeps1403]

Hi,

I am creating a weekly report wherein I use some formulaes to create the summary. I would like to know how to copy formulaes from one column to next on a button click and then remove formulaes from previous col. Help is much appreciated.

Attached is a sample xls

[deeps1403]

Hi All,

Can anyone help me onto this?

To give in more detail

COL D has formulaes already.
Step1:Now on button click, I would need to move formulaes from Col D to Col E( next blank column)
Step2:remove formulaes from Col D ( make the data static)

[bagullo]

hi,

i'm no expert, but I can give it a shot

oldc = 4 'or last column presumable
newc = 5 'or last column +1

for each dr in ws.rows(firstr & ":" & lastr).rows

      dr.columns(newc).formula = dr.columns(oldc).formula 'this will copy exactly the same
                                 'formula, not sure if it will move references with it if any present
      dr.columns(oldc).value = dr.columns(oldc).value

next dr

[arlu1201]

This is what you need

sub copy_formulae()
    Columns("D:D").Copy
    Columns("E:E").Select
    ActiveSheet.Paste
    Columns("D:D").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

You can get codes by recording the macro. Then you can tweak as required.

[royUK]

    Dim NextCol As Long
    NextCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Cells(1, NextCol).Copy Cells(1, NextCol + 1)
    Cells(1, NextCol).Value = Cells(1, NextCol).Value

Adjust this to suit your range

    Dim NextCol As Long
    NextCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Range(Cells(1, NextCol), Cells(Rows.Count, NextCol).End(xlUp)).Copy Cells(1, _
                                                                              NextCol + 1)
    Range(Cells(1, NextCol), Cells(Rows.Count, NextCol).End(xlUp)).Value = Range(Cells(1, NextCol), Cells(Rows.Count, NextCol).End(xlUp)).Value