VBA to copy data of selected headers only from all the sheets of another workbook

[Gaurangg]

Dear Friends,

I know, there could be lot of posts available for above mentioned query. And I have also tried most of them, however I am failed to match with my required result.

As I have to download a system generated report file (i.e. Daily Report File) and I need to collate the data from it to another workbook, (can say - "Master Dashboard" file). I always get an pop up stating the mismatch in format and extension of the file as the downloaded file have .xls extension. And while click on "Yes", I could open the file.

Also the worksheet count in Daily report file and count of columns are also not constant in Daily Report file. Hence I have identified few common columns which are also not on same place in every worksheet. And hence I am trying to build a code to search the data based on the specific header name and copy the column data and paste into Master file. However I face many errors and struggle to find where I am lacking.

I accept that I am not familiar with the variables and how to use it. However I have tried my best by seeing other codes. Please, it will be great if anyone can help to modify my code and run it smoothly.

Below is my code:

Sub GetHeaderData()

Dim TWK As Workbook
Dim Mypath As String
Dim TargetWS, CWs As Worksheet
Dim i As Long, CRng As Range
Dim Hdrs As Integer
Dim TargetHeader As Range


Set TWK = ThisWorkbook
Set TargetWS = TWK.Sheets("Processed_Raw_Collation")
Set CWs = TWK.Sheets("Main")
Set TargetHeader = TargetWS.Range("A1:F1")
'Hdrs = Array("CaseId", "ProcessName", "ClosedByName", "ClosedDate", "Rejection Reasons", "Case Received Date")
Dim SourceWS As Worksheet
Dim SourceCell As Range

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    ThisWorkbook.Activate
       
    Sheets("Main").Activate
    Mypath = Sheets("Main").Range("BA1").Value  'here the path updated of downloaded report
    Workbooks.Open Filename:=Mypath, Password:=Sheets("Main").Range("BA4").Value
   
    'I am getting the debug error here and while clicking on "End" the file got opened and macro started again from beginning
   
    NwFile = ActiveWorkbook.Name
    'Windows(NwFile).Activate
   
    Dim RealLastRow As Long
    Dim SourceCol As Integer
    ActiveWorkbook.Sheets(1).Activate
   
    For Each cell In TargetHeader
        If cell.Value <> "" Then
             Set SourceCell = Rows(SourceHeaderRow).Find _
                (cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
            If Not SourceCell Is Nothing Then
                SourceCol = SourceCell.Column
                RealLastRow = Columns(SourceCol).Find("*", LookIn:=xlValues, _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                If RealLastRow > SourceHeaderRow Then
                    Range(Cells(SourceHeaderRow + 1, SourceCol), Cells(RealLastRow, _
                        SourceCol)).Copy
                    TargetWS.Cells(2, cell.Column).PasteSpecial xlPasteValues
                    Sheets("Processed_Raw_Collation").Select
                    Dim Lr As Long
                    Lr = Sheets("Processed_Raw_Collation").Range("A" & Rows.Count).End(xlUp).Row
                    Range("A1:F" & Lr).Select
                   
                    ' to fill any blank cells with "-", so next data can be pasted against correct line items
                    Selection.Replace what:="", Replacement:="-", LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
                End If
            End If
        End If
    Next
   
 CWs.Activate

End Sub

Also uploading the images of the pop-ups I receive