Delete Sheet

Hi,

Need Experts help to add additional function in the attached script. Need to delete "Filter" sheet when run this sub. Currently the macro only deleting a "Data" sheet from the workbook.  
Sub exportWorkbook()
Dim fName As String
Dim wkb As Workbook
Dim wks As Worksheet
Dim mySht As Worksheet
Dim mySheets() As String
Dim I As Long
Dim newWkb As Workbook

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Data")
    
    fName = ThisWorkbook.Path & "\" & wks.Range("A3").Value & wks.Range("C3").Value & ".xls"
    
    For Each mySht In ThisWorkbook.Worksheets
        If mySht.Name <> "Data" Then
            ReDim Preserve mySheets(I) As String
            mySheets(I) = mySht.Name
            I = I + 1
        End If
    Next mySht
    
    ReDim Preserve mySheets(UBound(mySheets) - 1)
    
    ThisWorkbook.Sheets(mySheets).Copy
    Call RemoveAllMacros(ActiveWorkbook) 'in case there are macros in the sheet's codepages
    ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlExcel8
    ActiveWorkbook.Close
    
    MsgBox "Successful export of " & fName
    Application.DisplayAlerts = False
    Application.ScreenUpdating = True
    Call ExportPlanningSheet
    
End Sub

Open in new window

Billa7Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StephenJRCommented:
I think we need to see the code for RemoveAllMacros.
0
Billa7Author Commented:
Hi StephenJR,

Attached the Macro.
Sub RemoveAllMacros(objDocument As Object)
' deletes all VBProject components from objDocument
' removes the code from built-in components that can't be deleted
' use like this: RemoveAllMacros ActiveWorkbook ' in Excel
' or like this: RemoveAllMacros ActiveWorkbookDocument ' in Word
' requires a reference to the
' Microsoft Visual Basic for Applications Extensibility library
Dim I As Long, l As Long
    If objDocument Is Nothing Then Exit Sub
    I = 0
    On Error Resume Next
    I = objDocument.VBProject.VBComponents.Count
    On Error GoTo 0
    If I < 1 Then ' no VBComponents or protected VBProject
        MsgBox "The VBProject in " & objDocument.Name & _
            " is protected or has no components!", _
            vbInformation, "Remove All Macros"
        Exit Sub
    End If
    With objDocument.VBProject
        For I = .VBComponents.Count To 1 Step -1
            On Error Resume Next
            .VBComponents.Remove .VBComponents(I)
            ' delete the component
            On Error GoTo 0
        Next I
    End With
    With objDocument.VBProject
        For I = .VBComponents.Count To 1 Step -1
            l = 1
            On Error Resume Next
            l = .VBComponents(I).CodeModule.CountOfLines
            .VBComponents(I).CodeModule.DeleteLines 1, l
            ' clear lines
            On Error GoTo 0
        Next I
    End With
End Sub

Open in new window

0
StephenJRCommented:
Sorry, misread your question. At the moment your code doesn't delete any sheets, it just copies all of them except Data to a new workbook. Are you saying you don't want Filter copied across either? If that's right just change line 19 to
If mySht.Name <> "Data" And mySht.Name <> "Filter" Then

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Billa7Author Commented:
Thanks StephenJR for the help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.