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