I Have used the following code in office 2003 but cannot get it to work in office 2007
Private Sub CommandButton1_Click()

Dim wkbk As Workbook, sh As Worksheet
     Worksheets("MANUAL TS").Copy
     Set wkbk = ActiveWorkbook
    ' get rid of all cell formulas
     ActiveWorkbook.Sheets("MANUAL TS").Unprotect Password:="troytempest1"
     For Each sh In wkbk.Worksheets
         With sh.UsedRange
             .Value = .Value
             ActiveSheet.OLEObjects("CommandButton1").Delete
             
            
             'protect sheet'
             ActiveWorkbook.Sheets("MANUAL TS").Protect Password:="troytempest1"


         End With
     Next

wkbk.SaveAs "C:\Documents and Settings\tempesjo\My Documents\Timesheets\ " & Sheets("MANUAL TS").Range("B5") & ""



 wkbk.Close
Sheet111.Activate
End Sub