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