Export sheet date format not correct

[dappy]

Morning folks,

So, i have a sheet that is exported from a workbook using workbook adds, the date format in the workbook is yyyy-mm-dd but the format in the sheet after export is lost and reverts back to general format. is it possible to copy a sheet to a new sheet and keep the format?

thanks in advance

Carl

 

[Joe4]

Please explain in more detail exactly how you are doing the export, and then how you are opening the new file.

 

[dappy]

thanks, I have this to export

 Set NewBook = Workbooks.Add
  Workbooks("LTE_batch_selection_3.xlsm").Worksheets("batch_output").Range("A1:ab1000").Copy
  NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
 ' Worksheets("Sheet1").Name = "Batch_" & myValue
    Dim bFileSaveAs As Boolean
    bFileSaveAs = Application.Dialogs(xlDialogSaveAs).Show

the new file opens automatically from here.

 

[Joe4]

I think maybe by using ".PasteSpecial (xlPasteValues)", you are copying only the values and not any of the formats.
What happens if you just use ".Paste" instead, which should brining the formatting allong with it?

If your intention is to turn the formulas into hard-coded values, try do a Copy -> Paste Special Values over top of itself AFTER doing the initial Copy/Paste.

 

[dappy]

mmm i tried the Paste only and had no output, sheet was empty.

Can you explain a bit more for the Copy -> paste special values?

 

[Joe4]

You can use the Macro Recorder to get most of the code you need.
I did that, and cleaned up the code to look something like this:

Sub MyCopy()

 Dim NewBook As Workbook

 Set NewBook = Workbooks.Add

    Workbooks("Book2.xlsm").Worksheets("batch_output").Range("A1:AB1000").Copy NewBook.Worksheets("Sheet1").Range("A1")
  
    Range("A1:AB1000").Copy
    Range("A1:AB1000").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("A:AB").EntireColumn.AutoFit
  
 ' Worksheets("Sheet1").Name = "Batch_" & myValue
    Dim bFileSaveAs As Boolean
    bFileSaveAs = Application.Dialogs(xlDialogSaveAs).Show
    
End Sub

 

[ZVI]

Also xlPasteValuesAndNumberFormats can be used instead of xlPasteValues

  Set NewBook = Workbooks.Add
  Workbooks("LTE_batch_selection_3.xlsm").Worksheets("batch_output").Range("A1:ab1000").Copy
  NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

 

[Joe4]

Also xlPasteValuesAndNumberFormats can be used instead of xlPasteValues

Even better than my solution!
I was not aware that option existed!

 

[dappy]

folks, that's great, they both work well thank you but..

is it possible to get the format yyyy-mm-dd instead of dd-mm-yyyy?

 

[Joe4]

is it possible to get the format yyyy-mm-dd instead of dd-mm-yyyy?

Since you are using VBA code, it would be pretty easy to add a line of code to format the columns any way you want.
Just turn on the Macro Recorder and record yourself applying your desired format to the columns you want, and you will have the code you need.
Just copy and paste that code into your current VBA code.