Sub MergeBD()
    Dim StrFile As String
    Dim NumOfFiles As Integer
    Dim filePath As String
    Dim SourceWb As Workbook, ws As Worksheet
    Dim TargetWb As Workbook
    Dim NumOfRows As Long, NumOfRowsInUnav As Integer
    Dim NumOfCol As Long
    Dim NumOfColInBD As Long, NumOfColInUnav As Integer
    Dim s As Integer
    Dim BDpath As String
    Dim NormRng As Range, NormCell As Range
    Dim SplitDate As Variant
    Dim SplitDateSvit As Variant
    Dim SvitCell As Range
    Dim NumOfColInBDmost As Integer
    Dim IsEmpty As String
    Dim RowNo As Long
    Dim ListOfRows As Integer
    Dim period As String
    Dim nextperiod As String

    NumOfFiles = 0
    NumOfRows = 2
    NumOfRowsInUnav = 2
    period = "Jul-19"
    nextperiod = "Aug-19"
    BDpath = "Y:\!Global\1.ADMIN\FINANCIAL TRACKING\TU BASE DATA\2019\merge\"

    Set TargetWb = ThisWorkbook
    TargetWb.Activate

    TargetWb.Sheets("Data").Range("d2:o150999").ClearContents
    TargetWb.Sheets("Data").Range("q2:u150999").ClearContents
    TargetWb.Sheets("Data").Range("x2:y150999").ClearContents
    TargetWb.Sheets("Data").Range("aa2:af150999").ClearContents
    TargetWb.Sheets("Data").Range("ai2:ai150999").ClearContents
    TargetWb.Sheets("System").Range("a1:a100").ClearContents

    ' load up all files in predefined BDpath folder
    StrFile = Dir(BDpath & "*.xls*")
    Do While Len(StrFile) > 0
        NumOfFiles = NumOfFiles + 1
        TargetWb.Sheets("System").Range("A" & NumOfFiles) = StrFile
        StrFile = Dir
    Loop

    Application.ScreenUpdating = False

    For s = 1 To NumOfFiles
        filePath = TargetWb.Sheets("System").Range("A" & s).Value
        If Not filePath Like "*XX*" And Not filePath Like "*Jakub*" And Not filePath Like "*Marketa*" And filePath Like "*Base_Data*" Then
            Set SourceWb = Workbooks.Open(BDpath & filePath, False, ReadOnly:=True)
            Application.DisplayAlerts = False
            'unprotect
            SourceWb.Sheets("Live Base Data ").Unprotect Password:="BD_pass"
            ' unhide all rows and columns
            SourceWb.Sheets("Live Base Data ").Cells.EntireColumn.Hidden = False
            SourceWb.Sheets("Live Base Data ").Cells.EntireRow.Hidden = False
            ' disable filter

            With SourceWb.Sheets("Live Base Data ")
                If (.AutoFilterMode And .FilterMode) Or .FilterMode Then
                  .ShowAllData
                End If
            End With

            ' Check which column has the most rows
            NumOfColInBD = 1
            For ListOfRows = 1 To 19
                With SourceWb.Sheets("Live Base Data ")
                    If NumOfColInBD < .Cells(.Rows.Count, ListOfRows + 3).End(xlUp).Row Then
                        NumOfColInBD = .Cells(.Rows.Count, ListOfRows + 3).End(xlUp).Row
                    End If
                End With
            Next

            'copy only if BD has more than 1 row
            If NumOfColInBD > 1 Then
                'copy all relevant data
                For NumOfColInBD = 2 To NumOfColInBD
                    If (Format(SourceWb.Sheets("Live Base Data ").Range("Q" & NumOfColInBD), "mmm-yy") = period Or _
                    Format(SourceWb.Sheets("Live Base Data ").Range("Q" & NumOfColInBD), "mmm-yy") = nextperiod) And _
                    Not SourceWb.Sheets("Live Base Data ").Range("S" & NumOfColInBD) = "cancelled" Then
                        TargetWb.Sheets("Data").Range("D" & NumOfRows & ":O" & NumOfRows).Value = SourceWb.Sheets("Live Base Data ").Range("D" & NumOfColInBD & ":O" & NumOfColInBD).Value
                        TargetWb.Sheets("Data").Range("Q" & NumOfRows & ":U" & NumOfRows).Value = SourceWb.Sheets("Live Base Data ").Range("Q" & NumOfColInBD & ":U" & NumOfColInBD).Value
                        TargetWb.Sheets("Data").Range("Y" & NumOfRows & ":Y" & NumOfRows).Value = SourceWb.Sheets("Live Base Data ").Range("Y" & NumOfColInBD & ":Y" & NumOfColInBD).Value
                        SourceWb.Sheets("Live Base Data ").Range("AA" & NumOfColInBD & ":AA" & NumOfColInBD).Copy
                        TargetWb.Sheets("Data").Range("AA" & NumOfRows & ":AA" & NumOfRows).PasteSpecial xlFormulas
                        'TargetWb.Sheets("Data").Range("AA" & NumOfRows & ":AA" & NumOfRows).Value = SourceWb.Sheets("Live Base Data ").Range("AA" & NumOfColInBD & ":AA" & NumOfColInBD).Value
                        TargetWb.Sheets("Data").Range("Af" & NumOfRows & ":Af" & NumOfRows).Value = SourceWb.Sheets("Live Base Data ").Range("Af" & NumOfColInBD & ":Af" & NumOfColInBD).Value
                        TargetWb.Sheets("Data").Range("AI" & NumOfRows & ":AI" & NumOfRows).Value = SourceWb.Sheets("Live Base Data ").Range("AG" & NumOfColInBD & ":AG" & NumOfColInBD).Value
                        TargetWb.Sheets("Data").Range("AJ" & NumOfRows & ":AJ" & NumOfRows).Value = SourceWb.Sheets("Live Base Data ").Range("AH" & NumOfColInBD & ":AH" & NumOfColInBD).Value

                        NumOfRows = NumOfRows + 1
                    End If
                Next NumOfColInBD

                SourceWb.Close savechanges:=False
                'NumOfRows = NumOfRows + NumOfColInBD - 1
            End If
        End If
    Next s


    ' normalize period to have always 1st day as a day
    Set NormRng = TargetWb.Sheets("Data").Range("Q2:Q" & NumOfRows)
    For Each NormCell In NormRng
        If Len(NormCell.Value) > 3 Then
            NormCell.Value = Format(NormCell.Value, "mmm-yy")
            SplitDate = Split(NormCell.Value, "/")
            NormCell.Value = SplitDate(0) & "/1/" & SplitDate(2)
        End If
    Next NormCell


    Application.DisplayAlerts = True
    MsgBox "Copying completed!"

End Sub