using programming to copy worksheets data only

[Guest]

Hi,

How can i use programming to copy all the worksheets from an excel file to
another excel file as data only (i do not want the formulas to be copied as
formulas but as data) ?

i can see there's copydatafromrecordset but it's not suitable and also there
the copy method from worksheet (this will copy the formula)

kindly advice

tks & rdgs

 

[Dave Peterson]

This copies the sheet (as-is) preserving formats and page setups and even the
code behind the worksheet. Then it comes back and pastes as values.

Option Explicit
Sub testme()

Dim fWks As Worksheet
Dim tWks As Worksheet
Dim fWkbk As Workbook
Dim tWkbk As Workbook

Set fWkbk = Workbooks("book1.xls")
Set tWkbk = Workbooks("book2.xls")

For Each fWks In fWkbk.Worksheets
fWks.Copy _
after:=tWkbk.Sheets(tWkbk.Sheets.Count)
Set tWks = ActiveSheet
fWks.Cells.Copy
tWks.Range("a1").PasteSpecial Paste:=xlPasteValues
Next fWks

Application.CutCopyMode = False

End Sub


It might not do what you want--if you didn't want all that other stuff coming
with it.