Delete Sheets in workbook when saved

[Logan]

I have a workbook template that has 32 sheets in it. Is it
possible to have a macro delete specific sheets if certain
conditions are met when saved.
i.e. if N32 = "Adult" delete sheets 4,5,7,15(Sheet Names)
if N32 = "Youth" delete sheets 6,9,18, 23
I have the following 'Save As' macro and was wondering if
it could be incorporated into it.


Sub Save_As()
Dim FName1, FName2, FName3, Fullname
FName1 = "CK0"
FName2 = Range("AU2").Value & "-"
FName3 = Range("J4").Value
Fullname = FName1 & FName2 & FName3
Application.DisplayAlerts = False
ChDir "C:\Arrest"
ActiveWorkbook.SaveAs Fullname, FileFormat _
:=xlNormal, CreateBackup:=False, _
Accessmode:=xlShared
MsgBox "Saved to " & CurDir & " - " & Fullname
End Sub



Thanks

 

[Bob Phillips]

Hi Logan,

Untested, but should work (famous last words<G>)

Dim FName1 As String, FName2 As String, FName3 As String, Fullname As String
FName1 = "CK0"
FName2 = Range("AU2").Value & "-"
FName3 = Range("J4").Value
Fullname = FName1 & FName2 & FName3
Application.DisplayAlerts = False
ChDir "C:\Arrest"
If ActiveSheet.Range("N32").Value = "Adult" Then
Worksheets(Array("Sheet4", "Sheet5", "Sheet7", "Sheet15")).Delete
ElseIf ActiveSheet.Range("N32").Value = "Youth" Then
Worksheets(Array("Sheet6", "Sheet9", "Sheet18", "Sheet123")).Delete
End If
ActiveWorkbook.SaveAs Fullname, _
FileFormat:=xlNormal, _
CreateBackup:=False, _
Accessmode:=xlShared
MsgBox "Saved to " & CurDir & " - " & Fullname


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Logan]

Thanks Bob

-----Original Message-----
Hi Logan,

Untested, but should work (famous last words<G>)

Dim FName1 As String, FName2 As String, FName3 As String, Fullname As String
FName1 = "CK0"
FName2 = Range("AU2").Value & "-"
FName3 = Range("J4").Value
Fullname = FName1 & FName2 & FName3
Application.DisplayAlerts = False
ChDir "C:\Arrest"
If ActiveSheet.Range("N32").Value = "Adult" Then
Worksheets(Array("Sheet4", "Sheet5", "Sheet7", "Sheet15")).Delete
ElseIf ActiveSheet.Range("N32").Value = "Youth" Then
Worksheets(Array("Sheet6", "Sheet9", "Sheet18", "Sheet123")).Delete
End If
ActiveWorkbook.SaveAs Fullname, _
FileFormat:=xlNormal, _
CreateBackup:=False, _
Accessmode:=xlShared
MsgBox "Saved to " & CurDir & " - " & Fullname


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.

 

[Logan]

Bob the macro is not deleting the sheets

Question; should I be putting in the Sheet tab name(May) or
the Sheet number (Sheet13)?

The macro still runs fine but it doesn't delete any sheets.

 

[Logan]

My apologies, Bob. It works fine
Error on the one operating the keyboard.

Thanks for the help