-
April 29th, 2011, 03:53 PM
#1
Essbase Retrieve All Code
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
-
April 29th, 2011, 05:19 PM
#2
Re: Essbase Retrieve All Code
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?
Code:
For y = 2 To Num_Sheets
Please use code tags when posting code.
Ah, and... Welcome to CodeGuru!
I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.
This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.
-
May 2nd, 2011, 08:43 AM
#3
Re: Essbase Retrieve All Code
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
-
May 2nd, 2011, 08:59 AM
#4
Re: Essbase Retrieve All Code
Strange. Worked perfectly for me.
Then maybe something like this?
Code:
For y = 1 To Num_Sheets
If Sheets(y).Name <> "Lookup" Then
' ...
End If
Next
I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.
This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.
-
May 2nd, 2011, 09:11 AM
#5
Re: Essbase Retrieve All Code
ok i will try now and let you know. thanks
-
May 2nd, 2011, 09:14 AM
#6
Re: Essbase Retrieve All Code
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
-
November 26th, 2012, 05:41 PM
#7
Re: Essbase Retrieve All Code
Code:
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.
Code:
If Not Sheets(y).Name = "Lookup" And _
Not sheets (y).Name = "Test" Then
Always use [code][/code] tags when posting code.
-
November 26th, 2012, 06:06 PM
#8
Re: Essbase Retrieve All Code
Thank you! that works now.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|