VBA - Essbase Retrieve and Save Multiple Files

RedTangoX

New Member
Joined
Apr 6, 2021
Messages
1
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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!


VBA Code:
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"
Contact Sheet.png



 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Forum statistics

Threads
1,144,422
Messages
5,724,227
Members
422,543
Latest member
Bravo661

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top