Todays little conundrum copy worksheet.

[Nygie]

Morning all

Using what I have learned, today I managed to copy a range of one sheet to a new sheet preserving formatting using the paste special method. This sheet is then copied to a new workbook that opens, saves and closes using the below code.
This method although very simple does not seem to duplicate the formatting like manually copying a sheet to a new workbook. Is there a simple addition to this I am missing that would preserve the formatting or should I look at another way of doing it, like copy and paste special to a new workbook.

Thanks again.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Application.DisplayAlerts = False[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ThisWorkbook.Sheets("PMimp").Copy
ActiveWorkbook.SaveAs Filename:="X:\CIS\Fire Iinstallations\Temp\PMimp.csv", FileFormat:=xlCSV, CreateBackup:=True
ActiveWorkbook.Close[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Application.DisplayAlerts = True[/FONT]

 

[Joe4]

Exactly what kind of formatting are you talking about?
It looks like you are saving to a CSV file, and CSV files do not have formatting (they are straight text files).

 

[Nygie]

Exactly what kind of formatting are you talking about?
It looks like you are saving to a CSV file, and CSV files do not have formatting (they are straight text files).

Hi thanks for your reply Joe. You are indeed correct about the text file. I just checked the resulting file in notepad, it is fine.... never thought to check it in notepad before.
It's when you open the csv file in excel it is treating some of the cells as custom date cells for some reason i.e. MAR01 is formatting as date MAR-01 (01/03/2001). This obviously is of no consequence as long as the csv is correct.
Thanks for the brainstorming.

 

[Joe4]

You are welcome.

Yes, you should always use a Text Editor to view any sort of Text file to see what it really contains. The problem with Excel (especially with CSV files), is that Excel doesn't simply open the file and display the data "as-is". Rather, it does its own data conversions (what it this it should do).

One of my biggest pet peeves with Microsoft is that they determined that Excel should be the default program to view CSV files (because of what I said above). One of the first things I do when getting a new computer is change that default program to a Text Editor. One should NOT use Excel to view CSV files, if you really want to know what the data in the CSV file really looks like.

 

[Nygie]

Thanks Joe, I have never come across this with excel and csv files before. I know for future reference though.
Thanks

 

[Joe4]

The thing that I see people have the most problem with is custom date formats and leading zeroes, as opening in Excel will drop those things.