Sub compareFinalPlates()

 

Application.Calculation = xlCalculationAutomatic

 

'Remember time when macro starts

  StartTime = Timer

 

Application.ScreenUpdating = True

Application.DisplayAlerts = False

 

 

 

 

 

 xmsg = MsgBox("Has the Format of the current Final Plate changed since the previous week's format ", vbQuestion + vbYesNo, Title:="PLease Read Carefully!!!!!!!!!!!")

    If xmsg = vbYes Then

        MsgBox ("Please Email Mostafa.nejad@tnsgloabl before restarting this macro , This Macro is currently invalid")

            Exit Sub

    End If

    

 

crntMonth = Application.InputBox("Please Enter the Current Month")

 

 

sDirDefault = ":\Profit Centres\Internal\Mail Centre Products\Postbox Database Current\" & crntMonth & " " & "2017"

 

cDrive = Left((ThisWorkbook.Path), 1)

ChDrive Left((ThisWorkbook.Path), 1)

ChDir (cDrive & sDirDefault)

''''''''''

finalPlate1 = Application.GetOpenFilename(Title:="Please select the most recent final Plate")

finalplate2 = Application.GetOpenFilename(Title:="Please select last week's final Plate")

finalPlate3 = Application.GetOpenFilename(Title:="Please select final Plate previous to last week's")

 

If finalPlate1 = finalplate2 Or finalplate2 = finalPlate3 Or finalPlate1 = finalPlate3 Then

    MsgBox "you have chosen two identical files- Exiting", vbCritical + vbOKOnly

    Exit Sub

End If

 

 

 

If finalPlate1 = False Or finalplate2 = False Or finalPlate3 = False Then Exit Sub

 

 

 

fp1 = Mid(finalPlate1, InStrRev(finalPlate1, "\") + 1, InStrRev(finalPlate1, "x") - InStrRev(finalPlate1, "\"))

fp2 = Mid(finalplate2, InStrRev(finalplate2, "\") + 1, InStrRev(finalplate2, "x") - InStrRev(finalplate2, "\"))

fp3 = Mid(finalPlate3, InStrRev(finalPlate3, "\") + 1, InStrRev(finalPlate3, "x") - InStrRev(finalPlate3, "\"))

 

 

Workbooks.Open (finalPlate1)

 

 

''''''''getting the Months Name''''''''''''''''''

cfp = Mid(fp1, InStrRev(fp1, ".") - 4, (InStrRev(fp1, "x") - InStrRev(fp1, " ") - 5))

Lastfp = Mid(fp2, InStrRev(fp2, ".") - 4, (InStrRev(fp2, "x") - InStrRev(fp2, " ") - 5))

pfp = Mid(fp3, InStrRev(fp3, ".") - 4, (InStrRev(fp3, "x") - InStrRev(fp3, " ") - 5))

 

'''''''''''''''''''''''''''''''''''''''''''''''''

 

Workbooks(fp1).Activate

fc = Range("a1").End(xlToRight).Offset(0, 1).Column

Range(Cells(1, fc), Cells(1, fc)).Value = "M-F Vs Requested M-F"

Range(Cells(1, fc + 1), Cells(1, fc + 1)).Value = "TT-Sat1 Vs Requested Sat"

Range(Cells(1, fc + 2), Cells(1, fc + 2)).Value = "Comment"

Range(Cells(1, fc + 3), Cells(1, fc + 3)).Value = "TT-MM-FF1 " & Lastfp

Range(Cells(1, fc + 4), Cells(1, fc + 4)).Value = "TT - SAT1  " & Lastfp

Range(Cells(1, fc + 5), Cells(1, fc + 5)).Value = "Match MM-FF " & cfp & " vs " & Lastfp

Range(Cells(1, fc + 6), Cells(1, fc + 6)).Value = "Match SAT " & cfp & " vs " & Lastfp

Range(Cells(1, fc + 7), Cells(1, fc + 7)).Value = "Comment"

Range(Cells(1, fc + 8), Cells(1, fc + 8)).Value = "TT-MM-FF1 " & pfp

Range(Cells(1, fc + 9), Cells(1, fc + 9)).Value = "TT - SAT1  " & pfp

Range(Cells(1, fc + 10), Cells(1, fc + 10)).Value = "Match MM-FF " & cfp & " vs " & pfp

Range(Cells(1, fc + 11), Cells(1, fc + 11)).Value = "Match SAT " & cfp & " vs " & pfp

Range(Cells(1, fc + 12), Cells(1, fc + 12)).Value = "Comment"

Range(Cells(1, fc + 13), Cells(1, fc + 13)).Value = " Final Comment"

 

Range("a1", Range("a1").End(xlToRight)).Select

With Selection.Interior

    .Pattern = xlSolid

    .PatternColorIndex = xlAutomatic

    .Color = 6299648

End With

With Selection.Font

    .ThemeColor = xlThemeColorDark1

    .TintAndShade = 0

End With

With Selection.Font

    .Name = "Arial"

    .Size = 10

End With

 

 

'''''''''''''''''''''''''''Getting the data from the previous Final Plates'''''''''''''''''''''''''''''''''''''''''''''''''

Range(Cells(2, fc), Cells(2, fc)).Formula = "=H2=L2"

Range(Cells(2, fc + 1), Cells(2, fc + 1)).Formula = "=I2=M2"

Range(Cells(2, fc), Cells(2, fc + 1)).AutoFill Destination:=Range(Cells(2, fc), Cells(Cells(Rows.Count, 1).End(xlUp).Row, fc + 1))

Range(Cells(2, fc), Cells(Cells(Rows.Count, 1).End(xlUp).Row, fc + 1)).Copy

Range(Cells(2, fc), Cells(2, fc)).PasteSpecial xlPasteValues

 

'''Comment formula

Range("u2").Formula = "=IFERROR(IF(AND(S2=TRUE,T2=TRUE),""No Difference"",IF(AND(S2=TRUE,T2=FALSE),""Saturday updated"",IF(AND(S2=FALSE,T2=TRUE),""Mon-Fri updated"",""Mon-Sat updated""))),""PostBox not in Previous FP"")"

Range("u2").AutoFill Destination:=Range("u2:U" & Cells(Rows.Count, 1).End(xlUp).Row)

Range("u2:U" & Cells(Rows.Count, 1).End(xlUp).Row).Copy

Range("u2").PasteSpecial xlPasteValues

 

''''''''Getting the data from the previous Final Plate''''

Workbooks.Open (finalplate2)

Workbooks(fp1).Activate

 

Range("v2").Formula = "=VLOOKUP(A2,'[" & fp2 & "]Sheet1'!$A:$I,8,FALSE)"

Range("w2").Formula = "=VLOOKUP(A2,'[" & fp2 & "]Sheet1'!$A:$I,9,FALSE)"

Range("v2:w2").AutoFill Destination:=Range("v2:w" & Cells(Rows.Count, 1).End(xlUp).Row)

 

 

 

 

Range("v2:w" & Cells(Rows.Count, 1).End(xlUp).Row).Copy

Range("v2").PasteSpecial xlPasteValues

 

Workbooks(fp2).Close savechanges:=False

 

 

Range("v2:w" & Cells(Rows.Count, 1).End(xlUp).Row).NumberFormat = "HH:MM"

Range("x2").Formula = "=V2=H2"

Range("y2").Formula = "=W2=I2"

Range("Z2").Formula = "=IFERROR(IF(AND(X2=TRUE,Y2=TRUE),""No Difference"",IF(AND(X2=TRUE,Y2=FALSE),""Saturday updated"",IF(AND(X2=FALSE,Y2=TRUE),""Mon-Fri updated"",""Mon-Sat updated""))),""PostBox not in Previous FP"")"

Range("x2:z2").AutoFill Destination:=Range("x2:z" & Cells(Rows.Count, 1).End(xlUp).Row)

Range("x2:z" & Cells(Rows.Count, 1).End(xlUp).Row).Copy

Range("x2").PasteSpecial xlPasteValues

 

 

 

''''''''''''''''''''''''''''''''''''Getting the data from the one before  previous Final Plate''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

 

 

Workbooks.Open (finalPlate3)

Workbooks(fp1).Activate

Range("AA2").Formula = "=VLOOKUP(A2,'[" & fp3 & "]Sheet1'!$A:$I,8,FALSE)"

Range("AB2").Formula = "=VLOOKUP(A2,'[" & fp3 & "]Sheet1'!$A:$I,9,FALSE)"

Range("AA2:AB2").AutoFill Destination:=Range("AA2:AB" & Cells(Rows.Count, 1).End(xlUp).Row)

 

 

 

Range("AA2:AB" & Cells(Rows.Count, 1).End(xlUp).Row).Copy

Range("AA2").PasteSpecial xlPasteValues

 

Workbooks(fp3).Close savechanges:=False

 

 

Range("AA2:AB" & Cells(Rows.Count, 1).End(xlUp).Row).NumberFormat = "HH:MM"

Range("AC2").Formula = "=AA2=H2"

Range("AD2").Formula = "=AB2=I2"

Range("AE2").Formula = "=IFERROR(IF(AND(AC2=TRUE,AD2=TRUE),""No Difference"",IF(AND(AC2=TRUE,AD2=FALSE),""Saturday updated"",IF(AND(AC2=FALSE,AD2=TRUE),""Mon-Fri updated"",""Mon-Sat updated""))),""PostBox not in Previous FP"")"

Range("AC2:AE2").AutoFill Destination:=Range("AC2:AE" & Cells(Rows.Count, 1).End(xlUp).Row)

Range("AC2:AE" & Cells(Rows.Count, 1).End(xlUp).Row).Copy

Range("AC2").PasteSpecial xlPasteValues

 

 

Range("AF2").Formula = "=IF(AND(AE2=""No Difference"",Z2=""No Difference"",U2=""No Difference""),""No Difference"",""Changed in the last 3 weeks"")"

Range("AF2").AutoFill Destination:=Range("AF2:AF" & Cells(Rows.Count, 1).End(xlUp).Row)

 

Range("AF2:AF" & Cells(Rows.Count, 1).End(xlUp).Row).Copy

Range("AF2").PasteSpecial xlPasteValues

 

Range("b2").Formula = "=countifs($A:$A,A2)"

Range("b2").AutoFill Destination:=Range("b2:b" & Cells(Rows.Count, 1).End(xlUp).Row)

 

 

 

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2"), _

    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _

    xlSortTextAsNumbers

With ActiveWorkbook.Worksheets("Sheet1").Sort

    .SetRange Range("A2:AF115573")

    .Header = xlNo

    .MatchCase = False

    .Orientation = xlTopToBottom

    .SortMethod = xlPinYin

    .Apply

End With

 

'Determine how many seconds code took to run

  SecondsElapsed = Round(Timer - StartTime, 2)

 

'Notify user in seconds

  MsgBox "This code ran successfully in " & SecondsElapsed & " Minutes", vbInformation

 

 

 

 

 

 

End Sub