deleting worksheets from a file which has macros in it........does not work.

Hello Welchs101,

I wrote a macro to make a copy of the workbook with the sheets and code you want. The new workbook stays open after it is created. You can easily write a macro to save this where you want. Copy this code into into a new VBA module in your workbook.

' Written: May 16, 2012
' Author:  Leith Ross
' Summary: Creates a copy of the workbook with only selected worksheets.
'          VBA code from various modules and UserForms is copied as well.

Option Explicit

Sub CopyWorkbook()

    Dim Code As String
    Dim FormName As Variant
    Dim FormNames As Variant
    Dim i As Long
    Dim NewVBProj As Object
    Dim NewWkb As Workbook
    Dim NewStdMod As Object
    Dim Wks As Worksheet

        Set NewWkb = Workbooks.Add
        
        ThisWorkbook.Worksheets("Prompt").Visible = xlSheetHidden
        
        ThisWorkbook.Worksheets("Prompt").Copy After:=NewWkb.Worksheets(NewWkb.Worksheets.count)
        ThisWorkbook.Worksheets("Four_Column_Defn").Copy After:=NewWkb.Worksheets(NewWkb.Worksheets.count)
                
        Application.DisplayAlerts = False
            For i = 3 To 1 Step -1
                NewWkb.Worksheets(i).Delete
            Next i
        Application.DisplayAlerts = True
        
            Set NewVBProj = NewWkb.Application.VBE.ActiveVBProject
        
                Set NewStdMod = NewVBProj.VBComponents.Add(ComponentType:=1)
                
                With ThisWorkbook.VBProject
                
                    With .VBComponents("ThisWorkbook").CodeModule
                        Code = .Lines(1, .CountOfLines)
                        NewVBProj.VBComponents("ThisWorkbook").CodeModule.AddFromString Code
                    End With
                    
                    With .VBComponents("Module2").CodeModule
                        Code = .Lines(1, .CountOfLines)
                        NewStdMod.CodeModule.AddFromString Code
                    End With
                    
                    FormNames = Array("DateFormB", "PDM", "UserForm1")
                    
                    For i = 0 To UBound(FormNames)
                        FormName = FormNames(i)
                        FormNames(i) = Environ("Temp") & "\" & FormName
                        
                        .VBComponents(FormName).Export FormNames(i)
                        NewVBProj.VBComponents.Import FormNames(i)
                        
                        Kill FormNames(i)
                        Kill FormNames(i) & ".frx"
                    Next i
                    
                End With
            
End Sub