Copy and paste special - values into new excel file

[vpidatala]

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.

 

[Jim Rech]

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
| 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.
|