Maybe just updating in place would be easier:

Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
With wks.UsedRange
.Value = .Value
End With
Next wks
End Sub

or

Option Explicit
Sub testme2()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
With wks.UsedRange
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Next wks


wrote:

Hi,

I have a SQL DTS package exporting data from SQL server to Excel. When
the data is exported to excel, each cell other with character data has
a single quote and some spaces followed by actual data.

I figured out that I can copy the entire sheet and pasting with paste
special with values radio button selected in an other sheet is giving
me clean data.

I am totally new to excel programming. Will some one please let me know
how can I write a Macro to create an other excel file and then copy
each sheet from my excel file and paste using paste special with
"values" radio button selected into the new file.

My original excel file has 6 sheets with lots of data in each.

Thanks in advance.

--

Dave Peterson