Save Macro convert date alert.

[twl2009]

Im using the below Macro to save a new sheet, it all works except for it brings up an alert re converting date are formats. Is there a way to programmatically make sure the new sheet has same date format so the warning doesn't come up.

Thanks

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff; min-height: 13.0px}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}span.s1 {color: #011993}span.s2 {color: #000000}</style>Sub SaveSheet()


    ActiveSheet.Range("D12").Select


    Application.ScreenUpdating = False


    Dim wsCopy As Worksheet, wsPaste As Worksheet
    Dim wb As Workbook
    Dim sFileName As String, sPath As String, sName As String
    
    sPath = "/Users/User/Desktop/"
    sFileName = Range("D8").Value & " " & Range("Q5").Value & " " & Format(Range("R7"), "mm-dd-yy")
    sName = Range("D8").Value & " Timesheet"
    Set wsCopy = ThisWorkbook.ActiveSheet
    Set wb = Workbooks.Add
    Set wsPaste = wb.Sheets(1)
    
    wsCopy.Cells.Copy
    wsPaste.Cells.PasteSpecial Paste:=xlPasteFormats
    wsPaste.Cells.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    
    wsPaste.Rows(3).Delete
    wsPaste.Rows(2).Delete
    wsPaste.Rows(1).Delete
 
    ActiveSheet.Range("D9").Select
    ActiveWindow.DisplayGridlines = False
    ActiveWindow.DisplayHeadings = False


    wsPaste.Name = sName
    wb.SaveAs FileName:=sPath & sFileName, FileFormat:=xlOpenXMLWorkbook


    ActiveWorkbook.Close


    Application.ScreenUpdating = True




End Sub

 

[DushiPunda]

Not sure of the alert you're receiving, but try changing:

wsPaste.Cells.PasteSpecial Paste:=xlPasteFormats
wsPaste.Cells.PasteSpecial Paste:=xlValues

to:

wsPaste.Cells.PasteSpecial Paste:=xlPasteAllUsingSourceTheme 'Or xlPasteAll

Unless there's a specific reason that you are doing it the way you're doing it. What is the exact wording on the alert you are receiving?

 

[twl2009]

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px 'Helvetica Neue'}</style>The workbook you are pasting to uses a different date system.

Dont convert - convert

 

[twl2009]

Im doing it the way it is so that I dont take formulas onto new sheet, just values