Getting the array list to from the file itself

Hi,
I have 2 new questions this time. I will post one Q here and other in another post since these are two different things
Hope you people can help me.

The below is my macro

PHP Code: 

Option Explicit

Sub cmdUpdate_Click()
    On Error GoTo ErrHandler
    
    Dim aError, aFiles, i As Integer
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With
    
    aFiles = Array("R.0011358_LF_Design Support CF -Air_Liquid_PCR (Liq Fil)_MSR.xlsx", "R.0007420_Singapore_MSR.xlsx", "R.0007440_DBU Houston_MSR.xlsx", _
                   "R.0007441_CPGF-AR_LR_HH_MSR.xlsx", "R.0007442_Telecom Project_MSR.xlsx", _
                   "R.0011735_CPGK-HR_MSR.xlsx", "R.0011740_CGT-Germany_MSR.xlsx", _
                   "R.0011354_EBU_MSR.xlsx", "R.0008490_CGT_MSR.xlsx", _
                   "R.0011352_CES-US_MSR.xlsx", "R.0007415_Config_MSR.xlsx", _
                   "R.0011356_PDCA_MSR.xlsx", "R.0007417_PPSC_MSR.xlsx", "R.0011358_Design Support CF -Air_Liquid_PCR (Air Fil)_MSR.xlsx")
                   
    strError = vbNullString
    
    'check for files:
    For i = LBound(aFiles) To UBound(aFiles)
        If Dir(ThisWorkbook.Path & "\" & aFiles(i)) = vbNullString Then
            strError = strError & vbLf & "File not found: '" & aFiles(i) & "'."
        End If
    Next i
    
    If strError <> vbNullString Then GoTo ErrHandler
    
    With ThisWorkbook
        GetFromWorkbook .Worksheets("Liq Fil"), "R.0011358_LF_Design Support CF -Air_Liquid_PCR (Liq Fil)_MSR.xlsx"
        GetFromWorkbook .Worksheets("Singapore"), "R.0007420_Singapore_MSR.xlsx"
        GetFromWorkbook .Worksheets("Houston"), "R.0007440_DBU Houston_MSR.xlsx"

        GetFromWorkbook .Worksheets("Telecom"), "R.0007442_Telecom Project_MSR.xlsx"
        GetFromWorkbook .Worksheets("CPGK HR"), "R.0011735_CPGK-HR_MSR.xlsx"

        GetFromWorkbook .Worksheets("CGT-Germany"), "R.0011740_CGT-Germany_MSR.xlsx"
        
       GetFromWorkbook .Worksheets("AF Mixture"), "R.0011859_Offshore support PG-US - AF mixer_MSR.xlsx"
       
        GetFromWorkbook .Worksheets("EBU"), "R.0011354_EBU_MSR.xlsx"
        GetFromWorkbook .Worksheets("CGT-UK"), "R.0008490_CGT_MSR.xlsx"
        GetFromWorkbook .Worksheets("CES-US"), "R.0011352_CES-US_MSR.xlsx"

        GetFromWorkbook .Worksheets("Config"), "R.0007415_Config_MSR.xlsx"
        GetFromWorkbook .Worksheets("PDCA"), "R.0011356_PDCA_MSR.xlsx"
        GetFromWorkbook .Worksheets("PPSC"), "R.0007417_PPSC_MSR.xlsx"
        GetFromWorkbook .Worksheets("Air Fil"), "R.0011358_Design Support CF -Air_Liquid_PCR (Air Fil)_MSR.xlsx"
                GetFromWorkbook .Worksheets("CPGF AR-LR"), "R.0007441_CPGF-AR_LR_HH_MSR.xlsx"
        
        .Worksheets("DU Dashboard").Activate
        Sheet23.CreatePowerPoint
    End With
    
ErrHandler:
    If strError <> vbNullString Then
        frmError.lstError.Clear
        aError = Split(strError, vbLf)
        For i = LBound(aError) + 1 To UBound(aError)
            frmError.lstError.AddItem aError(i)
        Next i
        frmError.Show
    End If

    With Application
        .DisplayAlerts = True
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
    If Err.Number <> 0 Then
        MsgBox Err.Number & ": " & Err.Description
    End If
End Sub 


In this as you can see, the file names in the below section

PHP Code: 

    aFiles = Array("R.0011358_LF_Design Support CF -Air_Liquid_PCR (Liq Fil)_MSR.xlsx", "R.0007420_Singapore_MSR.xlsx", "R.0007440_DBU Houston_MSR.xlsx", _
                   "R.0007441_CPGF-AR_LR_HH_MSR.xlsx", "R.0007442_Telecom Project_MSR.xlsx", _
                   "R.0011735_CPGK-HR_MSR.xlsx", "R.0011740_CGT-Germany_MSR.xlsx", _
                   "R.0011354_EBU_MSR.xlsx", "R.0008490_CGT_MSR.xlsx", _
                   "R.0011352_CES-US_MSR.xlsx", "R.0007415_Config_MSR.xlsx", _
                   "R.0011356_PDCA_MSR.xlsx", "R.0007417_PPSC_MSR.xlsx", "R.0011358_Design Support CF -Air_Liquid_PCR (Air Fil)_MSR.xlsx") 


and the tab and file names in the below sessions

PHP Code: 

        GetFromWorkbook .Worksheets("Liq Fil"), "R.0011358_LF_Design Support CF -Air_Liquid_PCR (Liq Fil)_MSR.xlsx"
        GetFromWorkbook .Worksheets("Singapore"), "R.0007420_Singapore_MSR.xlsx"
        GetFromWorkbook .Worksheets("Houston"), "R.0007440_DBU Houston_MSR.xlsx"

        GetFromWorkbook .Worksheets("Telecom"), "R.0007442_Telecom Project_MSR.xlsx"
        GetFromWorkbook .Worksheets("CPGK HR"), "R.0011735_CPGK-HR_MSR.xlsx"

        GetFromWorkbook .Worksheets("CGT-Germany"), "R.0011740_CGT-Germany_MSR.xlsx"
        
       GetFromWorkbook .Worksheets("AF Mixture"), "R.0011859_Offshore support PG-US - AF mixer_MSR.xlsx"
       
        GetFromWorkbook .Worksheets("EBU"), "R.0011354_EBU_MSR.xlsx"
        GetFromWorkbook .Worksheets("CGT-UK"), "R.0008490_CGT_MSR.xlsx"
        GetFromWorkbook .Worksheets("CES-US"), "R.0011352_CES-US_MSR.xlsx"

        GetFromWorkbook .Worksheets("Config"), "R.0007415_Config_MSR.xlsx"
        GetFromWorkbook .Worksheets("PDCA"), "R.0011356_PDCA_MSR.xlsx"
        GetFromWorkbook .Worksheets("PPSC"), "R.0007417_PPSC_MSR.xlsx"
        GetFromWorkbook .Worksheets("Air Fil"), "R.0011358_Design Support CF -Air_Liquid_PCR (Air Fil)_MSR.xlsx"
                GetFromWorkbook .Worksheets("CPGF AR-LR"), "R.0007441_CPGF-AR_LR_HH_MSR.xlsx" 


are hard coded. I would like to have it take those data from a list like in the attached excel sheet. I am planning to have the tab containing this list in the file where this macro is, So no external file reference here also. How to do that? Tried some solutions available in net. But failed. Please help

If your sample sheet was named DataSheet.

semi-tested.

Please Login or Register  to view this content.

Hi,
Sorry, The code that you gave is giving some error. I am uploading the actual files for your convinience. Hope this will be helpful

What this macro does/needs to do is, it will open the MSR files and copy paste the value (not formulas) which is given in Consolidated MSR to the relevant tabs in the Consolidated MSR file.
I have added the tab 'DataSheet' and listed the files and the tabs in the consolidated file in to which the data should be pasted as per the suggestion given by you.

Can you please have a look

Try this.

Please Login or Register  to view this content.

Hi,
Yes.. Wonderful....

Working fine... Just one thing, I am getting an error message as

HTML Code: 

How to remove that? But this message is coming after everything... So no issues, just annoying.. thats all....

What line is it occurring on?

Sorry, my mistake.
I left some extra empty columns in the table. When I removed that, the problem got solved.
Really Sorry....

Thanks again for your support.
I tried to add reputation to your profile. But getting the message that, you need to spread some reputation around before giving again. Will try again in 2 days.




When you get time, can you pls have a look at this also?