Dim WO As Workbook, SO As Worksheet ' old
Dim WN As Workbook, SN As Worksheet ' new
Set WO = ActiveWorkbook ' this is where we come from
Set WN = Workbooks.Add ' the new sheet with only values
For Each SO In WO.Worksheets
Set SN = WN.Worksheets.Add ' create new sheet
SN.Name = SO.Name ' copy name
SO.Cells.Copy ' copy all cells to clipboard
SN.[A1].PasteSpecial xlPasteValues ' paste values at A1
SN.[A1].PasteSpecial xlPasteFormats ' paste formats as well at A1
Next SO
End Sub
http://stackoverflow.com/questions/4194422/how-to-copy-values-only-us
