copy in to next coulmn and freeze orignal coulm

[kasbac]

Hi There

Would anyone be able to help me with a macro that copy/pastes the formulas found in the range D41:D63 in to E41:E63 and then convert the results in D41:D63 in to values.

The next time I run the macro it should take the range E41:E63, copy paste the formulas in to coulmn F41:F63 and then convert the results in E41:E63 in to values.

 

[VoG]

Try

Sub atest()
Dim LC As Long
LC = Cells(41, Columns.Count).End(xlToLeft).Column
With Range(Cells(41, LC), Cells(63, LC))
    .Copy Destination:=Cells(41, LC + 1)
    .Value = .Value
End With
End Sub

 

[kasbac]

hmm nothing seemed to happen when i ran the macro?

Please note that I have formulas/values in all cells in the range B40:L63

 

[VoG]

It should have copied the last column to the last column + 1.

If that isn't what you want, how does the macro determine which column to copy?

 

[kasbac]

The starting point should be coulmn D, from there on everytime time i run the macro the starting point should move one coulmn to the right, is this possible?

If not the only option that I see is if the macro can look in to the formulas in the cells. The starting coulmn will always have "=$D$27" in line 41??

 

[VoG]

Try

Sub atest()
Dim LC As Long, Found As Range
Set Found = Rows(41).Find(what:="=$D$27", LookIn:=xlFormulas, lookat:=xlWhole)
If Found Is Nothing Then
    MsgBox "Formula not found"
    Exit Sub
End If
LC = Found.Column
With Range(Cells(41, LC), Cells(63, LC))
    .Copy Destination:=Cells(41, LC + 1)
    .Value = .Value
End With
End Sub

 

[kasbac]

Great! worked like a charm very much appreciated!