vba code to convert formulas into text.

[winmaxservices1]

I have data in sheet1. Will be adding columns and rows in sheet1 time to time.’
I want to take the entire data from sheet1 to sheet 2 (exact cell to cell) by using the formula “=SUBSTITUTE(CLEAN(TRIM(Sheet1!A1)),CHAR(160),"")”

Please refer attachement

After taking the data in sheet2, the data should convert into text format(formula should be removed)

[PCI]

You can do it by yourself very easely:
Just launch the macro record
and select all cells in sheet2, then Paste Special value

Please Login or Register  to view this content.

[winmaxservices1]

Paste special function does not remove the spaces which resemble character. hence using substitute formula

[PCI]

Not sure to get it right.
Is the result sent correct?
What is your need: Prepare the formulas in sheet2 or just freeze the values

[winmaxservices1]

Ok. Need two separate code

1) Prepare the forumals in sheet2

2) copy paste spacial values sheet2

[PCI]

See here

Please Login or Register  to view this content.

[winmaxservices1]

Cool boss. gr8.........

[winmaxservices1]

Also can you give me a code for the below job

Select the entire sheet (Sheet2)
Copy, Paste special Special and Values (Just to remove the SUBSTITUTE formula)

OR any simple code to remove all the formula from the sheet2.