Brainstorm for ideas how to handle user error

[TheRedCardinal]

Hi all,

I am at a loss about how to handle a common occurrence in my latest macro development.

The macro workbook (Wbk1) selects a file from a subfolder, called data, opens it, allocates it to a workbook variable WBk2, and then directly copies a sheet that is either called ICA or ICD from Wbk2 into Wbk1, a simple copy and paste job.

My user is required to prepare Wbk2 in advance by loading in the new data, refreshing the pivot table, then extracting the data from the pivot table for product lines ICA and ICD into separate worksheets, called ICA and ICD. This is the normal double click on the value method of extracting from the pivot table.

Problem is, they often forget, and up until now, this leads to a crash of the macro when it can't find the sheet.

Can I ask for any ideas on how to handle, and code this?

What I would like is for the macro to identify that the sheet it is looking for isn't present; then ideally, could it go on and extract the relevant sheet from the pivot table, rename it, and proceed without any input from the user? But for this I am not sure how to perform the extraction itself, or how to identify the sheet that has just been created so as to rename it.

Or secondly, to effectively pause, and allow the user to create the sheets (with a helpful message saying what is missing) - but how do I allow this? How could I create a button for the user to press once he has fixed the mistake?

Or is there a better way that I've not thought of yet?

 

[rlv01]

The place to start is with a simple message to your user that something is wrong. Example:

    Dim WS As Worksheet
    Dim NeededSheets As Variant, SH As Variant
    Dim SheetList As String

    NeededSheets = Array("ICA", "ICD")

    For Each WS In Wbk2.Worksheets                    'Workbook "Wbk2" is assumed to be present and already opened by your code prior to this statement
        SheetList = SheetList & WS.Name & "$"
    Next WS

    For Each SH In NeededSheets
        If InStr(1, SheetList, SH & "$") = 0 Then
            MsgBox "Required data sheet '" & SH & "' is missing. Please correct and re-try."
            Exit Sub
        End If
    Next SH