Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

For Each (If Next) Next

  Asked By: Ryan    Date: Aug 11    Category: MS Office    Views: 900
  

I am
working coding some VBA macros in excel to save other ESCE teachers hours of
time in scoring a complicated form that the state is requiring. The
completed project will be distributed for free.

I have run into a problem. I want to gather data from all the worksheets in a
workbook except for two "Sheet1" and "Sample". I was hoping to do this with
something like this:
Dim wSht As Worksheet
For Each wSht In Worksheets
If wSht.Name = "Sheet1" Then
Next
End If
If wSht.Name = "Sample" Then
Next
End If
'Code for rest of sheets
Next wSht

The two Next statements nested in Ifs of course don't work. Is there some
magical word I can use instead ? Or is there another way to cycle through
(activate) and pull data from all the worksheets in a workbook except 2.

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Angelica Ramos     Answered On: Aug 11

Rather than looking for Sheet1 or Sample, look for everything else...

Dim wSht As Worksheet
For Each wSht In Worksheets
If wSht.Name <> "Sheet1" and wSht.Name <> "Sample" Then
-'Code for rest  of sheets
End if
Next

 
Answer #2    Answered By: Lonnie Rogers     Answered On: Aug 11

My own preference for this type of thing is to use a Select statement.

I'd also change the name to Uppercase so there was no mistakes in the
compare.

This would look something like...

For Each wSht In Worksheets
Select case Ucase(wSht.Name)
Case "SHEET1", "SAMPLE"
'Do Nothing

Case Else
'Code for rest  of sheets

End Select
Next wSht

 
Answer #3    Answered By: Hubba Akhtar     Answered On: Aug 11

You guys gave me to go ways to fix the problem!

 
Didn't find what you were looking for? Find more on For Each (If Next) Next Or get search suggestion and latest updates.




Tagged: