Exporting columns into new workbook as text values not formulas

**ryanexceln00b** · 05-30-2013, 11:14 AM

hey, long time reader, first time poster.

I am trying to export columns filled with formulas such as =NOW() into a new workbook but I want the text value as it appears visually to be pasted over and not the formula.

Currently, the =NOW() formula is modified in excel using the format cell option to display =NOW() as YYYYMMDD. For example today, I want to paste to =NOW() as text in the new workbook to appear as 20130530 (no cell formatting can be used in the new workbook.

Below is what I currently have, but want the displayed text copied over and not the formulas/formatting.

Please Login or Register  to view this content.

**krjoshi** · 05-30-2013, 11:37 AM

Hi ryanexceln00b,

change the code to

Please Login or Register  to view this content.

for paste values

**billstpierre79** · 05-30-2013, 11:38 AM

You were very close. Try this.

Please Login or Register  to view this content.

**ryanexceln00b** · 05-30-2013, 11:46 AM

Originally Posted by billstpierre79

You were very close. Try this.

Please Login or Register  to view this content.

Unfortunately, this does not accomplish my goal.

In my workbook, I have a column with the formula =NOW(). This is formatted to appear visually as "20130530"

When I amend that code you gave me, the result in the new book displays the value as "41424.487" and not the desired "20130530"

**krjoshi** · 05-30-2013, 11:52 AM

Try this

Please Login or Register  to view this content.

**ryanexceln00b** · 05-30-2013, 12:00 PM

Close but not there yet.

While visually it looks fine, it is because the formats of the cells were copied over as well--which I cannot use.

The solution I need will have the same effect as me copying and pasting the =NOW() column into a Notepad document and the copying and pasting the column from the Notepad document back into excel.

**billstpierre79** · 05-30-2013, 12:36 PM

this might solve your problem
use this on the cell before you copy and paste

Please Login or Register  to view this content.

**ryanexceln00b** · 05-30-2013, 01:06 PM

Originally Posted by billstpierre79

this might solve your problem
use this on the cell before you copy and paste

Please Login or Register  to view this content.

Bill, thank you, this seems promising, but, as my name indicates, I am a n00b at vba.

In the code below, the two =NOW() columns are Column Y and Column BL. Where would I insert the code that you provided in my below script?

Please Login or Register  to view this content.

**billstpierre79** · 05-30-2013, 01:42 PM

Try

Please Login or Register  to view this content.

**ryanexceln00b** · 05-31-2013, 08:37 AM

I found a solution.

Instead of doing extra programming work in the macro, I fixed the problem from the source file.

Where I had =NOW() in Column A, I created a Column B with the formula =TEXT(A2, "yyyymmdd") and then used the same vba code above to start at column B instead of A.

This solved the issue.

Thank you, i need to tweak it a little bit. but this is great--thanks Bill!