Any reason not to paste the data right on top of itself (the usual way).

Sub a()
Dim WS As Worksheet
For Each WS In Worksheets
WS.Cells.Copy
WS.Cells(1).PasteSpecial xlPasteValues
Next
End Sub


--
Jim
wrote in message
oups.com...
| Hi,
|
| I am completely new to excel programming. I have a SQL DTS which
| creates an excel file and exports data into it. The created excel file
| could have variable number of work sheets.
|
| Text data in the excel file had a leading single quote character and
| spaces before the data. I can get rid of these by copying entire sheet
| and paste special--values into a new sheet.
|
| Would some one tell me how to automate to create a new excel file and
| copy and paste special (with values option) all the work sheets into
| the new file? I appreciate any help.
|