How can I refresh multiple sheets (in specific order) in an Excel workbook using EPM references?

Question

I have a workbook with about 50 sheets to be refreshed in a certain order (to avoid #rfr errors, since the sheets build off of one another).

The refresh is done via the EPM add in for Excel. I have activated the FPMXLclient functions and have attempted to write some code. I am very inexperienced with coding and logic. In the workbook the macro needs to start at the last tab, wait for the sheet to refresh, then move on to the next tab (and so on...). Below is an example of some of the VBA code I have written:

    Dim refreshList
        refreshList = Array("BS Analytic", "Balance Sheet")
        'There are more than just the 2 in the array (~50)
    Sub test_loop()
        Dim I
        For I = LBound(refreshList) To UBound(refreshList)
        MsgBox refreshList(I)
        Next I
    End Sub
    'Vba to refresh data
    Dim client As New EPMAddInAutomation
    Sub Refresh_Click()
        client.Refresh
    End Sub
    Sub AFTER_REFRESH()
        MsgBox "done"
    End Sub

Other info: This involves BPC and SAP too.

Answer 1

Why not have each sheet number in the array refreshList and then use For each I in refreshList. That will then propagate the refreshList in the order for each update. If it is also moving from the last sheet backwards you could always do the following:

Sub Refresh_Click

Dim refreshList() As Integer
reDim refreshList(50)
for i = 0 to 49
    refreshList(i) = 50 - i
next
For each I in refreshList
    Sheets(I).EnableCalculation = false
    Sheets(I).EnableCalculation = true
Next
MsgBox "Done"

End Sub

Answer 2

Assuming that your addin refreshes the active sheet, something like this in your loop might work:

Dim Sh As Worksheet
Set Sh = WorkSheets(RefreshList(I))
Sh.Activate
Client.Refresh