Save As issue
 

I have the following code that deletes sheets based on
values in certain cells.
The problem is, I would like the option of deleting others
sheets based on information given by the person filling out
the form. I would like a message box to pop up that the
person can answer yes or no to. If the answer is no than
delete Sheets 1, 5, 7.
I would like to incorperate into the following macro.

Sub Save_As()
Dim FName1 As String, FName2 As String
Dim FName3 As String, Fullname As String
FName1 = "CK0"
FName2 = Range("AU2").Value & "-"
FName3 = Range("J4").Value
Fullname = FName1 & FName2 & FName3
Application.DisplayAlerts = False
ChDrive "G"
ChDir "G:\New"
With ActiveSheet
If .Range("BJ31").Value = "No" Then
Worksheets(Array("Sheet16", " Sheet26", "
Sheet31")).Delete
ElseIf .Range("BJ31").Value = "Yes" Then
Worksheets(Array("Sheet15")).Delete
End If
If .Range("N32").Value = "Adult" Then
Worksheets(Array("Sheet19, " Sheet20", "
Sheet21", " Sheet22", " Sheet23", " Sheet24")).Delete
ElseIf .Range("N32").Value = "Youth" Then
Worksheets(Array("Sheet14", " Sheet15", "
Sheet17", " Sheet7")).Delete
End If
End With
ActiveWorkbook.SaveAs Fullname, _
FileFormat:=xlNormal, _
CreateBackup:=False, _
Accessmode:=xlShared
MsgBox "Saved to " & CurDir & " - " & Fullname

End Sub

Save As issue
 

Logan,

The basic code would be something like

Dim Result As Long
Result = MsgBox("Answer yes or no.",vbYesNo)
If Result = vbNo Then
Worksheets(Array("Sheet1","Sheet5","Sheet7")).Dele te
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Logan" wrote in message
...
I have the following code that deletes sheets based on
values in certain cells.
The problem is, I would like the option of deleting others
sheets based on information given by the person filling out
the form. I would like a message box to pop up that the
person can answer yes or no to. If the answer is no than
delete Sheets 1, 5, 7.
I would like to incorperate into the following macro.

Sub Save_As()
Dim FName1 As String, FName2 As String
Dim FName3 As String, Fullname As String
FName1 = "CK0"
FName2 = Range("AU2").Value & "-"
FName3 = Range("J4").Value
Fullname = FName1 & FName2 & FName3
Application.DisplayAlerts = False
ChDrive "G"
ChDir "G:\New"
With ActiveSheet
If .Range("BJ31").Value = "No" Then
Worksheets(Array("Sheet16", " Sheet26", "
Sheet31")).Delete
ElseIf .Range("BJ31").Value = "Yes" Then
Worksheets(Array("Sheet15")).Delete
End If
If .Range("N32").Value = "Adult" Then
Worksheets(Array("Sheet19, " Sheet20", "
Sheet21", " Sheet22", " Sheet23", " Sheet24")).Delete
ElseIf .Range("N32").Value = "Youth" Then
Worksheets(Array("Sheet14", " Sheet15", "
Sheet17", " Sheet7")).Delete
End If
End With
ActiveWorkbook.SaveAs Fullname, _
FileFormat:=xlNormal, _
CreateBackup:=False, _
Accessmode:=xlShared
MsgBox "Saved to " & CurDir & " - " & Fullname

End Sub

Save As issue
 

Thanks, Chip
That worked


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

The basic code would be something like

Dim Result As Long
Result = MsgBox("Answer yes or no.",vbYesNo)
If Result = vbNo Then
Worksheets(Array("Sheet1","Sheet5","Sheet7")).Dele te
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Logan" wrote in message
...
I have the following code that deletes sheets based on
values in certain cells.
The problem is, I would like the option of deleting others
sheets based on information given by the person filling out
the form. I would like a message box to pop up that the
person can answer yes or no to. If the answer is no than
delete Sheets 1, 5, 7.
I would like to incorperate into the following macro.

Sub Save_As()
Dim FName1 As String, FName2 As String
Dim FName3 As String, Fullname As String
FName1 = "CK0"
FName2 = Range("AU2").Value & "-"
FName3 = Range("J4").Value
Fullname = FName1 & FName2 & FName3
Application.DisplayAlerts = False
ChDrive "G"
ChDir "G:\New"
With ActiveSheet
If .Range("BJ31").Value = "No" Then
Worksheets(Array("Sheet16", " Sheet26", "
Sheet31")).Delete
ElseIf .Range("BJ31").Value = "Yes" Then
Worksheets(Array("Sheet15")).Delete
End If
If .Range("N32").Value = "Adult" Then
Worksheets(Array("Sheet19, " Sheet20", "
Sheet21", " Sheet22", " Sheet23", " Sheet24")).Delete
ElseIf .Range("N32").Value = "Youth" Then
Worksheets(Array("Sheet14", " Sheet15", "
Sheet17", " Sheet7")).Delete
End If
End With
ActiveWorkbook.SaveAs Fullname, _
FileFormat:=xlNormal, _
CreateBackup:=False, _
Accessmode:=xlShared
MsgBox "Saved to " & CurDir & " - " & Fullname

End Sub




.