making macro results permanent

**brownstuff** · 12-10-2009, 11:18 AM

Is there some way to render the macro/formula results in a worksheet/cell permanent. For example if you using macros to extrapolate resulting information like extracting first and last name from one cell into two cells, then is there a way to freeze the resulting data so the formula is no longer there.

Problem is arrising when I send the ws to someone else and they open the ws on their machine and the data gets distorted i.e the last name column is a list of &'s.

Ideally would like to leave row and column data resulting from macro operations in-tact and then remove all formulas or turn them off or something so that all is left is the data with no formulas.

Is this far fetched?

**DonkeyOte** · 12-10-2009, 11:46 AM

Odd that they should get different results - is it not a char based issue - ie certain characters not displaying in their locale ?

In general terms, yes you can write results over formulae, eg

Please Login or Register  to view this content.

that would take whatever was in A1:B10 at the time as a value and write it into the cell literally obliterating whatever formulae were there previously.

Does that help ?

**lopiner** · 12-10-2009, 12:29 PM

I don't know if this is useful to your project but you can put the result of a formula in a cell rather than the formula itself:

This will put the formula

Please Login or Register  to view this content.

This is the same thing but will output the value only

Please Login or Register  to view this content.

**DonkeyOte** · 12-10-2009, 01:20 PM

I'd add to that you don't always need to use Evaluate, eg

Please Login or Register  to view this content.

would do the same thing also ... calculating in memory certainly is another option.

(on an aside you could omit the WorksheetFunction bit if desired but generally it's believed to be slightly quicker than without - a few functions operate differently without the WorksheetFunction qualifier than with - eg Match)

**brownstuff** · 12-16-2009, 03:56 PM

Thanks man, not sure how to deploy this.
objective is to send one worsheet which has 7 columns of info over 1000 rows deep with all resulting data fixed and permanent. see what mean.

Some info for macro calculations rely on another ws data in same book.

Would what your telling me work for this application?

**Leith Ross** · 12-16-2009, 04:17 PM

Hello Brownstuff,

I would first create a copy of the worksheet.

1) Under Edit select "Move or Copy Sheet.."
2) Check the box "Create Copy", and click "OK"
3) Select all the cells on the original sheet by clicking the empty box next to column "A"
4) Copy them using Ctrl+C
5) Select the copied sheet and under "Edit" click "Paste Special..."
6) Click the option "Values" and click "OK"

This preserves the formatting and values from the calculations. If you want, this can be automated using VBA.

**brownstuff** · 12-19-2009, 11:17 AM

thanks that did the trick.
many thanks

**davesexcel** · 12-19-2009, 11:23 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save