I think: i somehow changed my default numr format to date

**romperstomper** · 06-30-2011, 08:42 AM

Assuming you are pasting to a column that already contains date data, format the whole column as Date and then just paste values. If not, you may have to stick with what you are doing now.

**welchs101** · 06-30-2011, 08:50 AM

fyi: searching this forum i found info on how to change the date format back to general

http://www.excelforum.com/excel-gene...n-its-own.html

basically you find the date format in question in the custom format and then delete it.

still not sure what it happened in the first place.

**welchs101** · 06-30-2011, 09:13 AM

the data i am copying is rather large and while i may be only interessted in a few cols of the data i have been instructed to keep ALL the data in the sheet. So i guess i will keep what i have for now..........because formatting individual cols sounds like a pain........do you think not formatting indidividual cols is ok?

Also, is there a way to check via vba if this custom format is present

[$-409]d-mmm-yyyy;@

this is the date format that caused the problem.

to fix it i had to remove/delete it from the custom format cells. actually i deleted all the custom formats which had [$-409 in them

**romperstomper** · 06-30-2011, 10:29 AM

You could try this:


Sub testing()
   Dim objStyle As Style
   For Each objStyle In ActiveWorkbook.Styles
      If objStyle.NumberFormat = "[$-409]d-mmm-yyyy;@" Then ActiveWorkbook.DeleteNumberFormat objStyle.NumberFormat
   Next objStyle
End Sub