Hi All - I am trying to create a macro to retrieve data for the same range on 20+ tabs using one click. The code below works in moving between the tabs and selecting the range, but the Essbase Retrieve is not working. Any assistance you could offer to help me figure out where I'm going wrong would be awesome. FYI - the tabs auto connect to the various Essbase db's needed via BI Launch Pad. Declare Function EssMenuVRetrieve Lib "ESSEXCLN.XLL" () As Long Sub loop_all_sheets() Num_Sheets = Application.Sheets.Count ' The number of sheets in the workbook are stored in the Num_Sheets variable For y = 1 To Num_Sheets 'This is a loop that will repeat Num_Sheets times. If Num_Sheets is 10 then the 'loop will execute 10 times. Sheets(y).Select Range("A1:O42").Select Application.Run macro:="EssMenuVRetrieve" Next y End Sub

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Essbase Retrieve for all Tabs in Workbook

Thread starter mrswalker1277
Start date Dec 5, 2017
Tags

essbase num_sheets range retrieve tabs

mrswalker1277

New Member

Dec 5, 2017

Hi All - I am trying to create a macro to retrieve data for the same range on 20+ tabs using one click. The code below works in moving between the tabs and selecting the range, but the Essbase Retrieve is not working. Any assistance you could offer to help me figure out where I'm going wrong would be awesome. FYI - the tabs auto connect to the various Essbase db's needed via BI Launch Pad.

Code:

Declare Function EssMenuVRetrieve Lib "ESSEXCLN.XLL" () As Long
Sub loop_all_sheets()
Num_Sheets = Application.Sheets.Count
' The number of sheets in the workbook are stored in the Num_Sheets variable
For y = 1 To Num_Sheets
'This is a loop that will repeat Num_Sheets times. If Num_Sheets is 10 then the
'loop will execute 10 times.
Sheets(y).Select
 Range("A1:O42").Select
Application.Run macro:="EssMenuVRetrieve"
Next y
End Sub