Elseif macro to skip macro and execute another macro if conditions are not met

earthworm

Active Member
Joined
May 19, 2009
Messages
307
I want to create a macro that will create new sheet and save the same if the value in particular cells are >0

I created one but its only saving one sheet at a time and not moving to second macro if the value in first cell is >0

Please assist

Code:
Sub if_1()If Range("c2").Value > 0 Then
    Workbooks.Add
    ChDir "C:\Users\_\Desktop"
    ActiveWorkbook.SaveAs Filename:="C:\Users\_\Desktop\01.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
        
ElseIf Range("c3").Value > 0 Then
       Workbooks.Add
    ChDir "C:\Users\_\Desktop"
    ActiveWorkbook.SaveAs Filename:="C:\Users\_\Desktop\02.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
   
ElseIf Range("c4").Value > 0 Then
       Workbooks.Add
    ChDir "C:\Users\_\Desktop"
    ActiveWorkbook.SaveAs Filename:="C:\Users\_\Desktop\03.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
   
      
ElseIf Range("c5").Value > 0 Then
       Workbooks.Add
    ChDir "C:\Users\_\Desktop"
    ActiveWorkbook.SaveAs Filename:="C:\Users\_\Desktop\04.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
   
End If
End Sub
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,251
Office Version
2007
Platform
Windows
You need independent if, in this way each one is evaluated.

Code:
Sub if_1()
    If Range("c2").Value > 0 Then
        Workbooks.Add
        ChDir "C:\Users\_\Desktop"
        ActiveWorkbook.SaveAs Filename:="C:\Users\_\Desktop\01.xlsx", FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
[COLOR=#0000ff]    End If[/COLOR]
    If Range("c3").Value > 0 Then
        Workbooks.Add
        ChDir "C:\Users\_\Desktop"
        ActiveWorkbook.SaveAs Filename:="C:\Users\_\Desktop\02.xlsx", FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
[COLOR=#0000ff]    End If[/COLOR]
    If Range("c4").Value > 0 Then
        Workbooks.Add
        ChDir "C:\Users\_\Desktop"
        ActiveWorkbook.SaveAs Filename:="C:\Users\_\Desktop\03.xlsx", FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
[COLOR=#0000ff]    End If[/COLOR]
    If Range("c5").Value > 0 Then
        Workbooks.Add
        ChDir "C:\Users\_\Desktop"
        ActiveWorkbook.SaveAs Filename:="C:\Users\_\Desktop\04.xlsx", FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
[COLOR=#0000ff]    End If[/COLOR]
End Sub
 

earthworm

Active Member
Joined
May 19, 2009
Messages
307
You need independent if, in this way each one is evaluated.

Code:
Sub if_1()
    If Range("c2").Value > 0 Then
        Workbooks.Add
        ChDir "C:\Users\_\Desktop"
        ActiveWorkbook.SaveAs Filename:="C:\Users\_\Desktop\01.xlsx", FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
[COLOR=#0000ff]    End If[/COLOR]
    If Range("c3").Value > 0 Then
        Workbooks.Add
        ChDir "C:\Users\_\Desktop"
        ActiveWorkbook.SaveAs Filename:="C:\Users\_\Desktop\02.xlsx", FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
[COLOR=#0000ff]    End If[/COLOR]
    If Range("c4").Value > 0 Then
        Workbooks.Add
        ChDir "C:\Users\_\Desktop"
        ActiveWorkbook.SaveAs Filename:="C:\Users\_\Desktop\03.xlsx", FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
[COLOR=#0000ff]    End If[/COLOR]
    If Range("c5").Value > 0 Then
        Workbooks.Add
        ChDir "C:\Users\_\Desktop"
        ActiveWorkbook.SaveAs Filename:="C:\Users\_\Desktop\04.xlsx", FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
[COLOR=#0000ff]    End If[/COLOR]
End Sub
Still not working its saving only 1 sheet

A1
B0
C12
D3

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

In above example three sheets should create example 1 ,3 & 4
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,251
Office Version
2007
Platform
Windows
In which cells are your data?
C2, C3, C4 y C5?

Or the data is in A1, B1, C1 and D1?
 

earthworm

Active Member
Joined
May 19, 2009
Messages
307
Data is in cell c column
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,251
Office Version
2007
Platform
Windows
:confused: is it worked?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,251
Office Version
2007
Platform
Windows
If you need to save a sheet, this instruction: Workbooks.Add, does not create a sheet, it creates a book with a sheet and it is the book that you save with it:

Code:
ActiveWorkbook.SaveAs Filename:="C:\Users\_\Desktop\01.xlsx", FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
Then when creating a new book, the sheet is blank, the next validation is done on the new book If Range ("c3"). Value> 0. the new book is blank so you do not create the next book.


Then you can:
a) Close the new book
b) Return to the book with the macro
c) Make reference to the book and the sheet of the book with the macro


I present the 3 options:

Option a)

Code:
Sub if_1_option_a()
    Dim wPath As String
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    wPath = "C:\Users\_\Desktop\"
    
    If Range("c2").Value > 0 Then
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=wPath & "01.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWorkbook.Close
    End If
    If Range("c3").Value > 0 Then
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=wPath & "02.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWorkbook.Close
    End If
    If Range("c4").Value > 0 Then
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=wPath & "03.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWorkbook.Close
    End If
    If Range("c5").Value > 0 Then
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=wPath & "04.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWorkbook.Close
    End If
End Sub
Option b)

Code:
Sub if_1_option_b()
    Dim wPath As String
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    wPath = "C:\Users\_\Desktop\"
    
    If Range("c2").Value > 0 Then
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=wPath & "01.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ThisWorkbook.Activate
    End If
    If Range("c3").Value > 0 Then
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=wPath & "02.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ThisWorkbook.Activate
    End If
    If Range("c4").Value > 0 Then
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=wPath & "03.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ThisWorkbook.Activate
    End If
    If Range("c5").Value > 0 Then
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=wPath & "04.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ThisWorkbook.Activate
    End If
End Sub

Option c)

Code:
Sub if_1_option_c()
    Dim wPath As String
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    wPath = "C:\Users\_\Desktop\"


    If ThisWorkbook.ActiveSheet.Range("c2").Value > 0 Then
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=wPath & "01.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    End If
    If ThisWorkbook.ActiveSheet.Range("c3").Value > 0 Then
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=wPath & "02.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    End If
    If ThisWorkbook.ActiveSheet.Range("c4").Value > 0 Then
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=wPath & "03.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    End If
    If ThisWorkbook.ActiveSheet.Range("c5").Value > 0 Then
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=wPath & "04.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    End If
End Sub

Choose the one that suits you
 

Watch MrExcel Video

Forum statistics

Threads
1,090,323
Messages
5,413,795
Members
403,502
Latest member
aaa33

This Week's Hot Topics

Top