Macro to show Which macro didnt work in a nested macro

Hi,
This time I have a different Problem and I am sure that you people can help me....

In the attached sheet, there are macros which will pull the data from the respective files allocated to it and update this master workbook.

I have updated a macro to do all those copy-pasting sheet by sheet.

What I need is, if the any of the macro fails, it should skip to other and in the end, it should tell me which are the macros failed without stopping the operation in the middle.

This is required to know that, which files are missing from the folder, so that I can take the followup of that, instead of searching everytime with the list.
New Consolidated MSR.zip

You could change your macros into functions and thus have the opportunity of returning a code which signifies the result.
Based on this returncode you can handle the rest of the macros.

Structure could be:

sub Consolidated()
    If Sheet10.openAndCopy_Liq() = returncode Then do something
    If Sheet11.openAndCopy_Singapore() = returncode Then do something
    etc ....

end sub

Alter sub OpenAndCopy_Liq() into Function OpenAndCopy_Liq()
and add (at the appropriate location) the following line to Function OpenAndCopy_Liq:

openAndCopy_Liq = returncode

You can use responses from the set statements to determine what returncode to set.

This will display all errors at the end in a ListBox: New Consolidated MSR.zip

BTW: As far as I saw you are doing the same for each of the sheets, just the target file is different. Why then just write one sub with parameters source and target?

Hi,

do not write "On Error GoTo 0" when there is error handling at the beginning, try New Consolidated MSR 2.zip

In the dashboard sheet I started fixing the powerpoint stuff (code is commented below your code), maybe you'd like to continue?

Hi,

the following was implemented:
1. Macro checks for all files, if file not found msgbox will appear and no copying will be done

2. Copy code rewritten into ONE sub in Module1

3. I have no idea about copying charts/shapes into powerpoint, for this you should open a new thread (in case everything else works)

File: New Consolidated MSR 2.zip

Contains:

Option Explicit

Private 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.0007418_Liq Fil_MSR.xlsx", "R.0007420_Singapore_MSR.xlsx", "R.0007440_Houston_MSR.xlsx", _
                   "R.0007441_CPGF-AR_LR_MSR.xlsx", "R.0007441_CPGF-HR_MSR.xlsx", "R.0007442_Telecom_MSR.xlsx", _
                   "R.0007443_CPGK-HR_MSR.xlsx", "R.0007444_CPGK-LR_MSR.xlsx", "R.0007814_CTT_MSR.xlsx", _
                   "R.0008500_QUIMPER_MSR.xlsx", "R.0007272_EBU_MSR.xlsx", "R.0008490_CGT_MSR.xlsx", _
                   "R.0007399_CES-US_MSR.xlsx", "R.0007400_CRTI_MSR.xlsx", "R.0007415_Config_MSR.xlsx", _
                   "R.0007416_PDCA_MSR.xlsx", "R.0007417_PPSC_MSR.xlsx", "R.0007418_Air Fil_MSR.xlsx")
                   
    'check for files:
    For i = LBound(aFiles) To UBound(aFiles)
        If Dir(ThisWorkbook.Path & "\" & aFiles(i)) = vbNullString Then
            MsgBox "File not found:" & vbLf & aFiles(i), vbOKOnly + vbInformation, "Error"
            GoTo ErrHandler
        End If
    Next i
                   
    strError = vbNullString
    
    With ThisWorkbook
        GetFromWorkbook .Worksheets("Liq Fil"), "R.0007418_Liq Fil_MSR.xlsx"
        GetFromWorkbook .Worksheets("Singapore"), "R.0007420_Singapore_MSR.xlsx"
        GetFromWorkbook .Worksheets("Houston"), "R.0007440_Houston_MSR.xlsx"
        GetFromWorkbook .Worksheets("CPGF AR-LR"), "R.0007441_CPGF-AR_LR_MSR.xlsx"
        GetFromWorkbook .Worksheets("CPGF HR"), "R.0007441_CPGF-HR_MSR.xlsx"
        GetFromWorkbook .Worksheets("Telecom"), "R.0007442_Telecom_MSR.xlsx"
        GetFromWorkbook .Worksheets("CPGK HR"), "R.0007443_CPGK-HR_MSR.xlsx"
        GetFromWorkbook .Worksheets("CPGK LR"), "R.0007444_CPGK-LR_MSR.xlsx"
        GetFromWorkbook .Worksheets("CTT"), "R.0007814_CTT_MSR.xlsx"
        GetFromWorkbook .Worksheets("Quimper"), "R.0008500_QUIMPER_MSR.xlsx"
        GetFromWorkbook .Worksheets("EBU"), "R.0007272_EBU_MSR.xlsx"
        GetFromWorkbook .Worksheets("CGT"), "R.0008490_CGT_MSR.xlsx"
        GetFromWorkbook .Worksheets("CES-US"), "R.0007399_CES-US_MSR.xlsx"
        GetFromWorkbook .Worksheets("CRTI"), "R.0007400_CRTI_MSR.xlsx"
        GetFromWorkbook .Worksheets("Config"), "R.0007415_Config_MSR.xlsx"
        GetFromWorkbook .Worksheets("PDCA"), "R.0007416_PDCA_MSR.xlsx"
        GetFromWorkbook .Worksheets("PPSC"), "R.0007417_PPSC_MSR.xlsx"
        GetFromWorkbook .Worksheets("Air Fil"), "R.0007418_Air Fil_MSR.xlsx"
        
        .Worksheets("DU Dashboard").Activate
        Sheet23.CreatePowerPoint
    End With

    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
    
ErrHandler:
    With Application
        .DisplayAlerts = True
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
    If Err.Number <> 0 Then
        MsgBox Err.Number & ": " & Err.Description
    End If
End Sub

and

Option Explicit

Public strError As String

Sub GetFromWorkbook(xlWsTrgt As Worksheet, strWbNameSrc As String)
    On Error GoTo ErrHandler
    
    Dim wsCopy As Worksheet
    Set wsCopy = Workbooks.Open(ThisWorkbook.Path & "\" & strWbNameSrc).Worksheets("Consolidated MSR")
    
    wsCopy.Columns("A:AV").Copy
    xlWsTrgt.Range("A1").PasteSpecial xlPasteValues
    
    wsCopy.Parent.Close False
    Exit Sub
ErrHandler:
    strError = strError & vbLf & "Error in '" & xlWsTrgt.Name & "' - " & Err.Number & ": " & Err.Description
    If Not wsCopy Is Nothing Then wsCopy.Parent.Close False
End Sub

and a Form showing errors in a listbox...

Is that ok? New Consolidated MSR 2.zip

Didn't I created a button on the summary sheet?

If you want to call this from a Form Button and want to see it in the Assign-macro selection, move the code to a standard module.

or

Create an ActiveX Button on the sheet, doubleclick it and moe the code into it.