Hi Guys,



I've searched high and low but can't seem to locate a solution, so here I am.

How can you dynamically convert a column containing formula results to static text?

Obviously it's easy to select the column and manually past values only, but how would you set this up for Excel to do it manually, ie. copy results to a new column ready for editing?

Thanks...

try a simple macro...
Sub Macrocomandă1()
Range("H10:H25").Select
Selection.Copy
Range("I10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Thanks Catalin I will check out and let you know how it goes....

HI Catalin,

Yes that macro works.

So how now do I set it up to run dynamically so that the contents of column 'H' (the formula results) are automatically copied into column 'I' (the static text for editing)? I can't seem to figure that out.

Thank you.

Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range ' The variable KeyCells contains the cells that will ' cause execution of macrocomandă1 when they are changed. Set KeyCells = Range("H10:H1000") If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then Macrocomandă1 End If End Sub

This macro will execute the first macro whenever a cell in range H10:H1000 is modified..

Hi Catalina,

Thanks for your post. However I can't get it work. Can you please take a look at my code and let me know what I did wrong? Thanks.

=================================================
Sub CopyFormulaResults()
Range("H10:H25").Select
Selection.Copy
Range("I10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

=================================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("H10:H25")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
CopyFormulaResults
End If
End Sub

enter this macros in a module :

Sub auto_open()
' works when you double click a cell and press enter, not when formula result changes, or when you copy formula to range
' whenever you need to update , double click on any cell then press enter
ThisWorkbook.Worksheets("Foaie2").OnEntry = "CopyFormulaResults"

End Sub

Sub CopyFormulaResults()
Range("H10:H50").Select
Selection.Copy
Range("I10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Hi Catalin,

Wow you're a genius!!! LOL Works perfect... oh and you're a life saver too as I really wasn't sure how I was going to get around this issue.

The only thing now is how can I execute this on the entire workbook?

I tried replacing: ThisWorkbook.Worksheets("TEST") =

with: ActiveWorkbook.OnEntry =

it works but I received an error when I re-opened the file again. Any ideas?

Thanks again... you rock!

replace sub auto open with this:

Sub auto_open()
Dim WS_Count As Integer
Dim i As Integer
WS_Count = ActiveWorkbook.Worksheets.Count
For i = 1 To WS_Count
ThisWorkbook.Worksheets(i).OnEntry = "CopyFormulaResults"
Next
End Sub

Hi Catalin,

Thanks that works.

Now I've got this working is there any way to make the process more automated?
As some people will probably complain if they have to double-click and enter after every change. Can we do a refresh on any cell change?

Thanks.

how much more automated? when they blink ?
you mean you never open a cell and enter data on this workbook?
Workbook_Change works only with data, does not work if your range contains formulas..this is a far more complicated macro

maybe an expert will give you a better solution

Yes 'on blinking' would be good... LOL

The column doesn't get updated when you change a cell, only when you do the double-click + ENTER process, I don't know anyone who does this, do you? Of course to do the dbl-click+enter every time you make a change just to update the results column would pretty much defeat the purpose of making it automated as its easier to click the macro button.

I though I read somewhere that there is a function to execute a macro on any cell change (Workbook wide) that would work because as soon as you enter the new information the column would update.

Any ideas? Tks...

you can setup cell A1 to decide if the macro should run.
info about events :http://www.cpearson.com/excel/events.htm
but will work only when you change the value in cell A1,it will not fire when a formula result changes, so this means it will work the same way
How do I run a macro every time a certain cell changes its value?

There is an event called Worksheet_Change which is triggered when a value is entered (it will not fire when a formula result changes). One of the arguments to this event is 'Target' which is a reference to what changed. Since this event will occur whenever a value changes - you can use the target to see if it is the cell you are interested in:

Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub Else 'Do whatever you need to do... End If End Sub

I don't know anyone who does this, except maybe, you...
Most of the people i know, between double click and enter, is typing data, or writing formulas...

Another idea for you is to run your macro every 30 seconds, or less ...

Sub CopyFormulaResults()
Application.OnTime Now + TimeValue("00:00:10"), "CopyFormulaResults"
Range("H10:H50").Select
Selection.Copy
Range("I10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

Hi Catalin,

Can your macro be formatted to copy a date to static text? If I use your formula I get the "40910" results in the copy to column.