Ifail 0 then

[KaaNunL]

Dim CurrentYear As Integer
Dim Previousweek As Integer
Dim Ingrows As Integer
Dim slaUK As String
Dim lFails As Long

CurrentYear = DatePart("yyyy", Date)
Previousweek = DatePart("ww", Date) - 1
slaUK = "SLA builder UK.xlsm"


Workbooks("SLA builder UK.xlsm").Activate
Sheets("SLA - Order delivery").Select
Selection.AutoFilter
ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=30, _
Criteria1:=CurrentYear
ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=29, _
Criteria1:=Previousweek
ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").Range.AutoFilter Field:=28, _
Criteria1:="Fail"


On Error Resume Next
lFails = ActiveSheet.ListObjects("tbl_SLA_OrderDelivery").DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count
On Error GoTo 0


If lFails = 0 Then MsgBox "Keep Calm and There were no FAILS this week "

Ingrows = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("C3:AL" & Ingrows).Copy

If CheckFileIsOpen("SLA_mitigation_ template_ v2_Blank.xlsx") = False Then

Workbooks.Open "N:\CCHU_IPO\Balázs\Reporting\Customer reports\DeutscheBank\Weekly\SLA\Hardware\SLA_mitigation_ template_ v2_Blank.xlsx"

End If

Workbooks("SLA_mitigation_ template_ v2_Blank.xlsx").Sheets("File to update").Activate

Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

ActiveWorkbook.SaveAs Filename:="N:\CCHU_IPO\Balázs\Reporting\Customer reports\DeutscheBank\Weekly\SLA\Hardware\xx_Mitigations\Mitigation uk de eu sent\UK\UK Order delivery SLA Fails - " & CurrentYear & " Week " & Previousweek & ".xlsx", FileFormat:=xlOpenXMLWorkbook



Call fEmailing


End Sub


Hi Friends,

i have just finished this macro, everything works well expect i have seen couple of things which i do not like

First of all if Ifail is 0 then i want macro to shut down without saving any workbook because some how it does still copy and paste and saving

Is there any way i can fix this ?

Thanks friends

 

[Fluff]

Change this

If lFails = 0 Then MsgBox "Keep Calm and There were no FAILS this week "

to

If lFails = 0 Then
   MsgBox "Keep Calm and There were no FAILS this week :)"
   Exit Sub
End If

 

[KaaNunL]

Change this

If lFails = 0 Then MsgBox "Keep Calm and There were no FAILS this week "

to

If lFails = 0 Then
   MsgBox "Keep Calm and There were no FAILS this week :)"
   Exit Sub
End If

Thanks mate it worked

 

[Fluff]

You're welcome & thanks for the feedback