Essbase Retrieve All Code

[Noorie007]

Hello,

I am trying to create a macro in order to be able to retrieve all sheets in a workbook. Here is a "loop through all" code I used. The problem is that I want to exclude 1 of the sheets from retrieval. How can I modify the following code to be able achieve that? The sheet I would like to exclude from the loop is called "Lookup" oe Sheet 1. Please help. Thanks.

Declare Function EssMenuVRetrieve Lib "ESSEXCLN.XLL" () As Long
Sub loop_all_sheets()
Num_Sheets = Application.Sheets.Count
For y = 1 To Num_Sheets
Sheets(y).Select
x = EssMenuVRetrieve
Sheets().Select
Next

End Sub

[Eri523]

You mean the sheet to exclude always is the first one in the Sheets collention? Then why not simply start looping with the first one after that?

For y = 2 To Num_Sheets

Please use code tags when posting code.

Ah, and... Welcome to CodeGuru!

[Noorie007]

Thank you.

I have tried y = 2 and it does not make a difference. I need like a select case code to exlcude sheet 1 from being retrieved.

thanks

[Eri523]

Strange. Worked perfectly for me.

Then maybe something like this?

  For y = 1 To Num_Sheets
    If Sheets(y).Name <> "Lookup" Then
      ' ...
    End If
  Next

[Noorie007]

ok i will try now and let you know. thanks

[Noorie007]

I am now getting a compile error Next without For. Here is what my code looks like now, can you point the error?

Declare Function EssMenuVRetrieve Lib "ESSEXCLN.XLL" () As Long
Sub loop_all_sheets()
Num_Sheets = Application.Sheets.Count

For y = 1 To Num_Sheets
If Sheets(y).Name <> "Lookup" Then
Sheets(y).Select
x = EssMenuVRetrieve
Sheets().Select
Next

End Sub

[DataMiser]

If Sheets(y).Name <> "Lookup" or _
sheets (y).Name <> "Test" Then

Think about the logic of that statement for a second.

If the sheet name is Test then it is <> Lookup so the If is true
If sheet name is Lookup then it is <> Test so again it will be true
If sheet name is anything else then it will be <> Test And <> Lookup so it will again be true

I can't tell what you are trying to exclude here btu your code will nto exclude anything. If those 2 sheets are the ones you want to exclude then the proper logic would be.

If Not Sheets(y).Name = "Lookup" And _
Not sheets (y).Name = "Test" Then

[ca_user]

Thank you! that works now.