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

 

[dappy]

thanks again but there lies the issue, i know how to format a range but i cant see how to add the format string into this

NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

 

[Joe4]

thanks again but there lies the issue, i know how to format a range but i cant see how to add the format string into this

Why do you want to use the Format string?
Note that doing that will convert the value from a date entry to a text one, which can really mess up calculations and sorting.
Most of the time, you just want to change the formatting of the columns.

To get the VBA code to do this, simply:
1. Turn on your Macro Recorder
2. Go to the column(s) you want formatted a certain way, and select them
3. Choose your desired formatting
4. Stop your Macro Recorder.

If you view your recorded code, you will have the VBA code that you need to add to your current code. Usually, you can clean it up a little bit, by getting rid of select statements and combining lines (though it is not necessary to do so).

So if your recorded code looks something like this:

    Range("C:C,E:E,G:G").Select
    Selection.NumberFormat = "yyyy-mm-dd"

You can simplify it to this:

    Range("C:C,E:E,G:G").NumberFormat = "yyyy-mm-dd"

Note that the Macro Recorder is a VERY useful too for getting snippets of code, and you should really get used to using it!

 

[dappy]

Ahhh got it, sorry for being a plum!

Its sorted now, thanks for your help and your patients :D

much appreciated
Carl

 

[Joe4]

You are welcome.