pastespecial with time date and numbe formats

[rjmdc]

hi
i lose my date/time and dollar cformats with the copy paste
what can i do to fix it?

Sub Export
()

Call RefreshPQActiveWorkbook

Call WSUnProtect(ActiveSheet)

Dim wb As New Workbook
Dim SaveFolder As String: SaveFolder = "M:\all\Workbook\"
Dim FileName As String
Dim FileDateTime As String: FileDateTime = Format(Now, "m-d-yy hh-mm")

With ActiveWorkbook.Worksheets("Expenses")
FileName = GetNewDescription(Worksheets("Expenses").Cells(1, "B")) & " " & Format(Worksheets("Expenses").Cells(2, "B"), "m_d_yyyy")
Set tbl = .ListObjects(1)
tbl.Range.Copy
End With

Set wb = Workbooks.Add
wb.Activate
wb.Sheets(1).Range("A1").PasteSpecial xlPasteAll
Selection.Columns.AutoFit

FullFileName = FileName & " (" & FileDateTime & ").xlsx"

'Save and Close New WB
wb.SaveAs SaveFolder & "\" & FullFileName, xlOpenXMLWorkbook
wb.Close SaveChanges:=False

'Back to Main WB
ThisWorkbook.Activate

Call FIFeeEmail

'Inform User
MsgBox "File Export Complete", vbInformation

End Sub
[/CODE]

 

[Alex Blakenburg]

This should work:

wb.Sheets(1).Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

I actually put the Paste Values with Number formatting button on my Quick Access Toolbar, I think in 90% of cases where people use paste values they would have been better off to use this option.