mardi 12 novembre 2019

Call on another sub during an If Statement

I have an if statement that calls on another sub once the criteria is met 'if isempty = true Then Call Sub FTE_Consolidation'

All of that works fine... however, once the FTE_Consolidation sub ends, the macro continues back to the first sub and continues the series of if statements. I don't want the macro to re-visit the 'Sub Emp_BY_PayPeriod' once I've called upon the 2nd Sub.

I've tried putting an End If at the end of 'Sub FTE Consoldation' Still gives me the "End If Without Block If"

Sub Emp_By_PayPeriod()

Dim Mws As Worksheet
Set Mws = Workbooks("Master_Load_File.xlsm").Worksheets("Sheet1")    

 Mws.Range("J:J").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("L1"), Unique:=True

If IsEmpty(Range("L2")) = True Then Call FTE_Consolidation

    Mws.Range("J1").AutoFilter Field:=10, Criteria1:=ActiveSheet.Range("L2").Value

    Mws.Range("A1").CurrentRegion.Copy
        Workbooks.Add.ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
                                  Range("A1").PasteSpecial Paste:=xlPasteAll
                      ActiveSheet.Range("A1").Select

    ActiveWorkbook.SaveAs "C:\Users\jmcgoldrick\Desktop\VBA Load FIle\Consolidated Files\IncPYRep_(Enter         Name1)_Load_(Enter Pay Period).xlsx", FileFormat:=51
ActiveWorkbook.Close


'Next Pay Period'
If IsEmpty(Range("L3")) = True Then Call FTE_Consolidation

    Mws.Range("J1").AutoFilter Field:=10, Criteria1:=ActiveSheet.Range("L3").Value

    Mws.Range("A1").CurrentRegion.Copy
        Workbooks.Add.ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
                                  Range("A1").PasteSpecial Paste:=xlPasteAll
                      ActiveSheet.Range("A1").Select

   ActiveWorkbook.SaveAs "C:\Users\jmcgoldrick\Desktop\VBA Load FIle\Consolidated Files\IncPYRep_(Enter Name2)_Load_(Enter Pay Period).xlsx", FileFormat:=51
   ActiveWorkbook.Close


'Next Pay Period'
 If IsEmpty(Range("L4")) = True Then Call FTE_Consolidation

    Mws.Range("J1").AutoFilter Field:=10, Criteria1:=ActiveSheet.Range("L4").Value

    Mws.Range("A1").CurrentRegion.Copy
        Workbooks.Add.ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
                                  Range("A1").PasteSpecial Paste:=xlPasteAll
                      ActiveSheet.Range("A1").Select

 ActiveWorkbook.SaveAs "C:\Users\jmcgoldrick\Desktop\VBA Load FIle\Consolidated Files\IncPYRep_(Enter Name3)_Load_(Enter Pay Period).xlsx", FileFormat:=51
ActiveWorkbook.Close

 End Sub


 Sub FTE_Consolidation()

Dim Mws As Worksheet
Set Mws = Workbooks("Master_Load_File.xlsm").Worksheets("Sheet1")

Mws.AutoFilter.ShowAllData

    Mws.Range("L1").CurrentRegion.Clear
    Mws.Range("L1").CurrentRegion.ClearFormats


'Breakout Consolidation, Summary, and VP to New Workbook'

'FTE Consolidation'
 Mws.Range("I1").AutoFilter Field:=9, Criteria1:="<>FTE"
 Mws.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
     Mws.AutoFilter.ShowAllData
     Mws.UsedRange.Copy

 Workbooks.Add.ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
                           Range("A1").PasteSpecial Paste:=xlPasteAll
               ActiveSheet.Name = "Consolidation"

ActiveWorkbook.SaveAs "C:\Users\jmcgoldrick\Desktop\VBA Load FIle\Consolidated Files\FTE Consolidated     Payroll Report (Enter_Date).xlsx", FileFormat:=51

Dim FTEws As Worksheet
Set FTEws = Workbooks("FTE Consolidated Payroll Report (Enter_Date).xlsx").Worksheets("Consolidation")

Worksheets("Consolidation").Activate
ActiveSheet.Range("A1").Select

ActiveWorkbook.Save
ActiveWorkbook.Close

Mws.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
MsgBox "Load Files Have Been Saved Successfully!"

Application.ScreenUpdating = True

End Sub

Aucun commentaire:

Enregistrer un commentaire