Exporting columns into new workbook as text values not formulas

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.

Sub Export()
    
    Dim strFileName As String
    
    strFileName = InputBox("Type a name for the new workbok", "File Name")
    If Trim(strFileName) = vbNullString Then Exit Sub
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Range("Y2:BL250").Copy
    Sheets.Add.Range("A1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    ActiveSheet.UsedRange.EntireColumn.AutoFit
    ActiveSheet.Move
    ActiveWorkbook.SaveAs "C:\test\" & strFileName & ".xls", xlOpenXMLWorkbookMacroEnabled
    ActiveWorkbook.Close False
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

You were very close. Try this.

Sheets.Add.Range("A1").PasteSpecial xlPasteValues

Originally Posted by billstpierre79

You were very close. Try this.

Sheets.Add.Range("A1").PasteSpecial xlPasteValues

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"

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.

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

Dim d As long
      d = Format(Now, "yyyymmdd")
range("Yourrange")=d

Originally Posted by billstpierre79

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

Dim d As long
      d = Format(Now, "yyyymmdd")
range("Yourrange")=d

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?

Sub Export()
    
    Dim strFileName As String
    
    strFileName = InputBox("Type a name for the new workbok", "File Name")
    If Trim(strFileName) = vbNullString Then Exit Sub
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Range("Y2:BL250").Copy
    Sheets.Add.Range("A1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    ActiveSheet.UsedRange.EntireColumn.AutoFit
    ActiveSheet.Move
    ActiveWorkbook.SaveAs "J:\shared\Swaps\Equity Finance\Ryan\" & strFileName & ".xls", xlOpenXMLWorkbookMacroEnabled
    ActiveWorkbook.Close False
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub