ExcelBanter - sheet backup

Try some code like the following. You'll need a reference to the VBA
Extensibility object library.

Sub AAA()
'!!!! requires a reference to:
' Microsoft Visual Basic for Applications Extensibility 5.3
' In VBA go to the Tools menu, choose References, and then
' select this item in the list.

Dim NewWB As Excel.Workbook
Dim NewWS As Excel.Worksheet
Dim SH As Excel.Shape
Dim OleObj As Excel.OLEObject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

ThisWorkbook.Worksheets("RP Log").Copy
' after the Copy, the newly created
' workbook becomes the Active workbook.
Set NewWB = ActiveWorkbook
Set NewWS = ActiveSheet

' get rid of any shapes and Forms controls
For Each SH In NewWS.Shapes
SH.Delete
Next SH
' get rid of OLE/ActiveX controls
For Each OleObj In NewWS.OLEObjects
OleObj.Delete
Next OleObj
' get rid of VBA code
For Each VBComp In NewWB.VBProject.VBComponents
Set CodeMod = VBComp.CodeModule
Select Case VBComp.Type
Case vbext_ct_Document
With CodeMod
On Error Resume Next
.DeleteLines 1, .CountOfLines
End With
Case Else
VBComp.Collection.Remove VBComp
End Select
Next VBComp

Application.DisplayAlerts = False
NewWB.SaveAs Filename:=ThisWorkbook.Path & "\RPLog.xls"
Application.DisplayAlerts = True
NewWS.Protect ' password:="password"
NewWB.Protect structu=True, Windows:=True ' ,password:="password"
NewWB.Close savechanges:=True
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 14 Sep 2009 21:21:29 +0100, "sunilpatel"
wrote:

Can someone please helpme.

I need to backup a sheet called "RP LOG" as a new workbook named "RPLOG".
I have 3 essential requirements though.

1. new workbook and sheet must be protected
2. row one which contains buttons linked to macros bust not be copied
3. the vba code copied across must be deleted.

my attempt below has 2 problems
One is that eventhough i delete row 1 the entire new row one acts as if the
buttons are still there (cursor turns to a hand and links to a macro which
is not in the new workbook resulting in an error)
My second problem is how do i delete the vba code on the new sheet.

Sub Backup()
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Copy
ActiveWorkbook.SaveAs "C:\RPLOG"
ActiveSheet.Unprotect "OLDPASSWORD"
Application.EnableEvents = False
Rows(1).Delete
Cells.Select
Selection.Locked = True
ActiveSheet.Protect "NEWPASSWORD"
Workbooks("RPLOG.XLS").Save
Workbooks("RPLOG.XLS").Close
End Sub