Todays little conundrum copy worksheet.

Nygie

New Member
Joined
Apr 15, 2015
Messages
40
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,708
Office Version
365
Platform
Windows
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

New Member
Joined
Apr 15, 2015
Messages
40
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,708
Office Version
365
Platform
Windows
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

New Member
Joined
Apr 15, 2015
Messages
40
Thanks Joe, I have never come across this with excel and csv files before. I know for future reference though.
Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,708
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,084,748
Messages
5,379,613
Members
401,615
Latest member
syn_excel

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top