Sub CopyBU()

    'Define date by input box
    today = InputBox("Input today date for file name, eg 20141124", "Input the date for file name", "yyyymmdd")
    datadate = InputBox("Input the data date, eg 201501", "Input the data date", "yyyymm")
    yr = Left(datadate, 4)
    mth = Right(datadate, 2)
    
'To find the MMM form of current month (mth)
        If mth = "01" Then
        longmth = "Jan"
        ElseIf mth = "02" Then
        longmth = "Feb"
        ElseIf mth = "03" Then
        longmth = "Mar"
        ElseIf mth = "04" Then
        longmth = "Apr"
        ElseIf mth = "05" Then
        longmth = "May"
        ElseIf mth = "06" Then
        longmth = "Jun"
        ElseIf mth = "07" Then
        longmth = "Jul"
        ElseIf mth = "08" Then
        longmth = "Aug"
        ElseIf mth = "09" Then
        longmth = "Sep"
        ElseIf mth = 10 Then
        longmth = "Oct"
        ElseIf mth = 11 Then
        longmth = "Nov"
        ElseIf mth = 12 Then
        longmth = "Dec"
        Else
        MsgBox ("Error")
        End If

'Copy from Marketing Service (MSD) file
    Workbooks.Open Filename:= _
        "\\hkrpa-o33\RPA-Dev-Repository\RPA-PC001\UWD\WeeklyRetentionRenewal\Division\UW-Solutions\Renewal Retention\" & yr & "\Weekly Tracking Report\Raw\Template - MSD.xlsx"
        '"S:\Division\UW-Solutions\Renewal Retention\" & yr & "\Weekly Tracking Report\Raw\Template - MSD.xlsx"

    Sheets("GL_").Select
    Rows("4:4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    
    Workbooks.Open Filename:= _
        "\\hkrpa-o33\RPA-Dev-Repository\RPA-PC001\UWD\WeeklyRetentionRenewal\Division\UW-Solutions\Renewal Retention\" & yr & "\Weekly Tracking Report\Raw\PJD - Marketing Service.xls"

    Rows("4:4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Template - MSD.xlsx").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Selection.End(xlDown).Select
    
'Insert EC Flag & Formula - Others
    Range("A:A").Insert
    Range("A3").Value = "EC Flag"
    Range("A3").Font.Bold = True
    Range("A4").Formula = "=IF(IFNA(VLOOKUP(VALUE(E4),'[EC_Policy (as at " & longmth & ").xlsx]Sheet1'!$A:$A,1,FALSE),)=0, "" "",""EC"")"
    Range("A4:A" & LastRow).FillDown
    Range("A:A").Copy
    Range("A:A").PasteSpecial Paste:=xlPasteValues
    ActiveCell.Offset(1, 0).Select
    Application.CutCopyMode = False
    Windows("PJD - Marketing Service.xls").Activate
    ActiveWorkbook.Close

'To refresh pivot table
    Windows("Template - MSD.xlsx").Activate
    Sheets("pivot").Select
    ActiveSheet.PivotTables(1).PivotCache.Refresh
    Range("A1").Select
    
'Amend date in summary
    Sheets("MSD").Select
    Range("A1").Select
    ActiveCell.Cells = "as at " & Left(today, 4) & "-" & Mid(today, 5, 2) & "-" & Right(today, 2)
    Range("B3").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues

'Define output path
    ActiveWorkbook.SaveAs Filename:= _
        "\\hkrpa-o33\RPA-Dev-Repository\RPA-PC001\UWD\WeeklyRetentionRenewal\Division\UW-Solutions\Renewal Retention\" & yr & "\Weekly Tracking Report\" & yr & "" & mth & "\BU\MSD - Renewal Status_GenLink_" & longmth & "_" & today & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

        
    ActiveWorkbook.Close

    MsgBox ("Done")

End Sub