Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save As csv Carraige Return issue | Excel Worksheet Functions | |||
save as issue | Excel Discussion (Misc queries) | |||
File Save/Save As Issue | Setting up and Configuration of Excel | |||
Save As Authentication Issue From ASP.NET | Excel Discussion (Misc queries) | |||
UserForm Save Issue | Excel Worksheet Functions |