VBA - Pick up data from same format in varies worksheets and paste to a master tracker

This code will get you started. It works on the idea that you would receive a daily report and file the workbook in which it is contained away in your fileing system. The code below will ask you to browse for it, open it, extract data and close it - all in the background.

Option Explicit

Sub UpdateMasterTracker()
    ' 013

    Dim WbS         As Workbook                 ' data Source
    Dim WasOpen     As Boolean                  ' WbS was open before
    Dim WsS         As Worksheet                ' Source: worksheet to read from
    Dim WsT         As Worksheet                ' Target: worksheet to write to
    Dim Rt          As Long                     ' row to write to in Ws
    Dim Tmp         As Variant                  ' working variable


    WasOpen = GetDataSource(WbS)
    If WbS Is Nothing Then Exit Sub                     ' user cancelled
    Set WsS = WbS.Worksheets(1)                         ' select the first sheet in WbS

    Application.ScreenUpdating = False
    Set WsT = ThisWorkbook.Worksheets("Report")         ' change name to match
    With WsT
        Rt = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 ' last used row in column A + 1
        Tmp = Split(WsS.Cells(1, 1).Value, ":")
        .Cells(Rt, "A").Value = Tmp(UBound(Tmp))        ' copy the date
        WsS.Range("A9:E9").Copy Destination:=.Cells(Rt, 2)
        WsS.Range("A12").Copy Destination:=.Cells(Rt, 7)
        WsS.Range("A15").Copy Destination:=.Cells(Rt, 8)
        WsS.Range("C15:E15").Copy Destination:=.Cells(Rt, 9)
        WsS.Range("A18").Copy Destination:=.Cells(Rt, 12)
    End With

    With Application
        .DisplayAlerts = False
        If Not WasOpen Then WbS.Close                   ' keep open if it was open
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

Private Function GetDataSource(WbS As Workbook) As Boolean
    ' 013
    ' WbS will be Nothing if cancelled by user
    ' return True if WbS was open

    Dim Ffn         As Variant                  ' Full File Name
    Dim Tmp         As Variant                  ' working variable


    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls", 1
        If .Show Then
            Ffn = .SelectedItems.Item(1)
        Else
            Exit Function                   ' user cancelled
        End If
    End With

    Application.ScreenUpdating = False
    Tmp = Split(Ffn, Application.PathSeparator)
    On Error Resume Next
    Set WbS = Workbooks(Tmp(UBound(Tmp)))
    If Err Then                             ' workbook isn't open
        Set WbS = Workbooks.Open(Ffn)
        ActiveWindow.Visible = False        ' hide the workbook
    End If
    Application.ScreenUpdating = True

    GetDataSource = Not CBool(Err.Number)
End Function

Two things you need to know about merged cells in Excel. 

1. The value of merged cells is always stored in the first cell of the merged range. All others are ignored.
2. Merged cells viciously interfere with Excel's natural order of rows and columns. Nothing good ever comes from them. There are plenty of alternatives. Use them all before you merge cells - and then change your wishes, but don't merge cells.