Attribute VB_Name = "DeleteAndCopyData"
Option Explicit

Sub copying_passkey_data()

'--------------'
' Version 1.09 '
'--------------'

Dim NumberOfLinesSCRPK As Long, NumberOfLinesSCRWFMH1 As Long, NumberOfLinesSCRWFMH2 As Long, NumberOfLinesSCRCS As Long
Dim NumberOfLinesSCRCP As Long
Dim NumberOfLinesBELPK As Long, NumberOfLinesBELWFM As Long, NumberOfLinesBELCS As Long, NumberOfLinesBELCP As Long
Dim StartDeleteSCRPK As Long, StartDeleteSCRWFMH1 As Long, StartDeleteSCRWFMH2 As Long, StartDeleteSCRCS As Long
Dim StartDeleteSCRCP As Long
Dim StartCopyBELPK As Long, StartCopyBELWFM As Long, StartCopyBELCS As Long, StartCopyBELCP As Long
Dim StopCopyBELWFM As Long
Dim FirstLineToDelete As Long, FirstLineToCopy As Long, LastLine As Long
Dim StartDelete As Long, StartCopy As Long
Dim DateOfDay As Date
Dim ActualDay As Integer, ActualMonth As Integer, ActualYear As Integer
Dim CalculatedMonth As Integer
Dim DisplayStatusBar As Boolean
Dim StartLocation As String
Dim BeScoreCardFile As String
Dim ScoreCardFile As String

    '------------------------------------------
    ' Initialize some variables used afterwards
    '------------------------------------------
    DisplayStatusBar = Application.DisplayStatusBar
    BeScoreCardFile = "DO TS Scorecard.xls"
    ScoreCardFile = "scorecard-data.xls"
    ActualDay = Day(Now)
    'ActualDay = 12
    ActualMonth = Month(Now)
    'ActualMonth = 11
    ActualYear = Year(Now)
    'ActualYear = 2007
    Debug.Print "Before checks: ", ActualDay, ActualMonth, ActualYear
    If ActualMonth = 1 Then
            ActualYear = ActualYear - 1
            CalculatedMonth = 12
    Else
        If ((ActualMonth <> 11) And (ActualDay <= 15)) Then
            CalculatedMonth = ActualMonth - 1
        Else
            CalculatedMonth = ActualMonth
        End If
    End If
    Debug.Print "After  checks: ", ActualDay, CalculatedMonth, ActualYear
    Application.DisplayStatusBar = True
    
    
    ' -----------------------------------------------------
    ' Sort WFM data based on 3 keys: Year - Month - Country
    ' -----------------------------------------------------
    Application.StatusBar = "Sorting WFM data in scorecard-data file" & Now
    If (((ActualMonth >= 11) Or (ActualMonth <= 4)) Or ((ActualMonth = 5) And (ActualDay <= 15))) Then
        NumberOfLinesSCRWFMH1 = Sheets("WFM Data H1").Cells(2 ^ 16, 1).End(xlUp).Row
        Worksheets("WFM Data H1").Select
        Range(Cells(2, 1), Cells(NumberOfLinesSCRWFMH1, 27)).Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:= _
            Range("B2"), Order2:=xlAscending, Key3:=Range("L2"), Order3:=xlAscending _
            , Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
            xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
            DataOption3:=xlSortNormal
    End If
    If ((ActualMonth >= 5) And (ActualMonth <= 10)) Then
        NumberOfLinesSCRWFMH2 = Sheets("WFM Data H2").Cells(2 ^ 16, 1).End(xlUp).Row
        Worksheets("WFM Data H2").Select
        Range(Cells(2, 1), Cells(NumberOfLinesSCRWFMH2, 27)).Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:= _
            Range("B2"), Order2:=xlAscending, Key3:=Range("L2"), Order3:=xlAscending _
            , Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
            xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
            DataOption3:=xlSortNormal
    End If
        
    ' ----------------------------------------------------------------------
    ' Delete WFM data from current and previous month (if day of month < 15)
    ' ----------------------------------------------------------------------
    Application.StatusBar = "Deleting WFM data in scorecard-data file" & Now
    If (((ActualMonth >= 11) Or (ActualMonth <= 4)) Or ((ActualMonth = 5) And (ActualDay <= 15))) Then
        StartDeleteSCRWFMH1 = 0
        For FirstLineToDelete = 2 To NumberOfLinesSCRWFMH1
            If Val(Worksheets("WFM Data H1").Cells(FirstLineToDelete, 1).Value) = ActualYear Then
                If Val(Worksheets("WFM Data H1").Cells(FirstLineToDelete, 2).Value) = CalculatedMonth Then
                    StartDeleteSCRWFMH1 = FirstLineToDelete
                    FirstLineToDelete = NumberOfLinesSCRWFMH1 + 1
                End If
            End If
        Next FirstLineToDelete
        If StartDeleteSCRWFMH1 <> 0 Then
            Debug.Print "SCR -  1st WFM H1 line to delete =", StartDeleteSCRWFMH1
            Debug.Print "SCR - Last WFM H1 line to delete =", NumberOfLinesSCRWFMH1
            Worksheets("WFM Data H1").Range(Cells(StartDeleteSCRWFMH1, 1), Cells(NumberOfLinesSCRWFMH1, 27)).Select
            Selection.ClearContents
            Application.CutCopyMode = False
        Else
            Debug.Print "SCR - Number Of WFM Lines =", NumberOfLinesSCRWFMH1, "No Lines to Delete on H1"
        End If
    End If
    
    If (ActualMonth = 5) Then
        StartDeleteSCRWFMH2 = 0
        For FirstLineToDelete = 2 To NumberOfLinesSCRWFMH2
            If Val(Worksheets("WFM Data H2").Cells(FirstLineToDelete, 1).Value) = ActualYear Then
                If Val(Worksheets("WFM Data H2").Cells(FirstLineToDelete, 2).Value) = ActualMonth Then
                    StartDeleteSCRWFMH2 = FirstLineToDelete
                    FirstLineToDelete = NumberOfLinesSCRWFMH2 + 1
                End If
            End If
        Next FirstLineToDelete
        If StartDeleteSCRWFMH2 <> 0 Then
            Debug.Print "SCR -  1st WFM H2 line to delete =", StartDeleteSCRWFMH2
            Debug.Print "SCR - Last WFM H2 line to delete =", NumberOfLinesSCRWFMH2
            Worksheets("WFM Data H2").Range(Cells(StartDeleteSCRWFMH2, 1), Cells(NumberOfLinesSCRWFMH2, 27)).Select
            Selection.ClearContents
            Application.CutCopyMode = False
        Else
            Debug.Print "SCR - Number Of WFM Lines =", NumberOfLinesSCRWFMH2, "No Lines to Delete on H2"
        End If
    End If
    
    If ((ActualMonth >= 6) And (ActualMonth <= 10)) Then
        StartDeleteSCRWFMH2 = 0
        For FirstLineToDelete = 2 To NumberOfLinesSCRWFMH2
            If Val(Worksheets("WFM Data H2").Cells(FirstLineToDelete, 1).Value) = ActualYear Then
                If Val(Worksheets("WFM Data H2").Cells(FirstLineToDelete, 2).Value) = CalculatedMonth Then
                    StartDeleteSCRWFMH2 = FirstLineToDelete
                    FirstLineToDelete = NumberOfLinesSCRWFMH2 + 1
                End If
            End If
        Next FirstLineToDelete
        If StartDeleteSCRWFMH2 <> 0 Then
            Debug.Print "SCR -  1st WFM H2 line to delete =", StartDeleteSCRWFMH2
            Debug.Print "SCR - Last WFM H2 line to delete =", NumberOfLinesSCRWFMH2
            Worksheets("WFM Data H2").Range(Cells(StartDeleteSCRWFMH2, 1), Cells(NumberOfLinesSCRWFMH2, 27)).Select
            Selection.ClearContents
            Application.CutCopyMode = False
        Else
            Debug.Print "SCR - Number Of WFM Lines =", NumberOfLinesSCRWFMH2, "No Lines to Delete on H2"
        End If
    End If
        
    Application.CutCopyMode = False
    Application.StatusBar = False
    
End Sub