Remove VBA sheet code when copying sheet to another workbook

[Spyke831]

Hi All,My question is this I wish to create an archieve of sheets that are deleted from an excel app, but dont want any of the sheet code to be copied with the sheet. I have the following code which runs but does not delete the code. Please help.Private Sub ArchiveRecord(SheetToArchive As String) Dim RemoveDate As String RemoveDate = Format((Date + 1095), "dd-mmm-yy") Application.ScreenUpdating = False Workbooks.Open ("\\AMO3\ROOTFS3\fashamm426\10QOGLR-LAD-28-FtrSectSNCO\New Folder (2)\RECS_ARCHIVE.xls") Workbooks("RECS DATA ENTRY v7.xls").Sheets(SheetToArchive).Copy After:=Workbooks("RECS_ARCHIVE.xls").Sheets("-") ActiveSheet.Name = ActiveSheet.Name & " (Delete on " & RemoveDate & ")" With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Index).CodeModule .DeleteLines 1, .CountOfLines End With ActiveWorkbook.Close True Application.ScreenUpdating = TrueEnd SubThanksMatt

 

[dmt32]

Hi,
as a suggestion if your sheets just contain event code why not consider moving the code to workbook events in the Thisworkbook code page - something like this:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Sheet1" Then
        'do stuff for sheet1
    End If
End Sub

this would then negate the need to try & delete sheet level code. Alternatively, you could copy the data to a template sheet for your archiving process.

Hope helpful

Dave

 

[Spyke831]

Hi,
as a suggestion if your sheets just contain event code why not consider moving the code to workbook events in the Thisworkbook code page - something like this:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Sheet1" Then
        'do stuff for sheet1
    End If
End Sub

this would then negate the need to try & delete sheet level code. Alternatively, you could copy the data to a template sheet for your archiving process.

Hope helpful

Dave

Thnaks Dave,

The code that is being copied over is from a command button that is on the sheet. I suppose I could remove the button atfer archiving the sheet but dont know how to do that with code either. Any suggestions greatly received

Matt