VBA - Essbase Retrieve and Save Multiple Files

[RedTangoX]

As with most people, I'm new to Essbase and I'm looking for some guidance.

I created a master template which will generate and then save a version for each department I oversee. To do this, I embed a macro which will:

1. Pull information from an Essbase Retrieve tab​

2. Populate that information in a Summary tab​

3. Hide zero sum rows on Summary Tab​

3. Save the information as it's own filename​

4. Unhide all rows, then move on to the next BU listed on a BU List tab and repeat​

​

For some reason, I keep running into an error at the end of the script: Run-time error '1004'. I've been trying to remove .Select from my code, which is reflected below, but maybe I didn't do this properly? I've inserted a scrubbed version of my BU list to give context to my code.

One final issue is the last line of code - application.statusbar. For some reason it stops at 86% and will not update the status beyond that. Any advice is appreciated!

Sub Save_All()

Application.ScreenUpdating = False

' Loop through all BUs on BU List Tab
    Dim x As Integer
' Set numrows = number of BUs to loop through (currently 68)
        numrows = Sheets("BU List").Range("B11", Range("B11").End(xlDown)).Rows.Count
                   'Old code was: 
                             'ActiveWorkbook.Sheets("BU List").Select
                             'numrows = Range("B11", Range("B11").End(xlDown)).Rows.Count
       
' Start loop at row 11 and loop through numrows BUs
    For x = 11 To numrows + 10
        'update B7 for each BU listed on BU List
            Sheets("BU List").Range("B7") = Cells(x, 2).Value
                    'Old Code was: 
                             'ActiveWorkbook.Sheets("BU List").Select
                              'Range("B7") = Cells(x, 2).Value
    
        'Refresh Essbase
            Call Retrieve_Summary

        'hide rows with only 0s in summary tab
             ActiveWorkbook.Sheets("Summary").Select
             Call hideZeros
                    
        ' Save copy of file
            Dim fileName, folderPath As String
            Sheets("BU List").fileName = Cells(x, 8).Value
            folderPath = Cells(x, 9).Value
            ActiveWorkbook.SaveCopyAs fileName:=folderPath & fileName
                        
        ' unhide all rows in summary tab
            Sheets("Summary").Rows.EntireRow.Hidden = False
            
            Application.StatusBar = Round((x - 11) / (numrows + 10) * 100, 0) & "% complete   /  " & Sheets("BU List").Cells(x, 2).Value
    Next

    Application.ScreenUpdating = True
End Sub

Screen Shot of the "BU List"

​

​