For/Next Loop through worksheets

[Guest]

Hi,

I am trying to call up formatting procedures to format sheets, but I don't
want to apply the formatting procedures to any sheet with one of the five
names as indicated in the sample code below. Why am I getting an error
message telling me "Next without For"? Is there a more efficient way to
execute this code?

Here is the sample code:

Set wb = ActiveWorkbook
Set sh = ActiveSheet
For Each ws In wb
If ws.Name = "SUMMARY" Then
If ws.Name = "Parameters" Then
If ws.Name = "PIVOTDATA" Then
If ws.Name = "PIV_Deliverables" Then
If ws.Name = "PIV_RC" Then
GoTo Here:
Call Formatting

Next ws

Here:
Application.Goto "Summary_Home"


Thanks in advance!

 

[Guest]

Set wb = ActiveWorkbook
Set sh = ActiveSheet
For Each ws In wb
If ws.Name <> "SUMMARY" and _
ws.Name <> "Parameters" and _
ws.Name <> "PIVOTDATA" and _
ws.Name <> "PIV_Deliverables" and
ws.Name <> "PIV_RC" Then
ws.Activate
Call Formatting
Next ws

Application.Goto "Summary_Home"

 

[Guest]

You have 5 then's without corresponding end ifs. For what you are doing I
like to use select case statements. then I can control each worksheet as
necessary.

dim wks as worksheet

for each wks in Worksheets
Select Case wks.Name
Case "SUMMARY"
Case "Parameters"
Case "PIVOTDATA"
Case "PIV_Deliverables"
Case "PIV_RC"
Case Else
Call Formatting(wks)
End Select
Next wks

 

[Guest]

Kent,

You need to have an "End If" for your If statements.. There are also some
other problems with your code.. Give this a try:

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "SUMMARY", "Parameters", "PIVOTDATA", "PIV_Deliverables",
"PIV_RC"
'do nothing
Case Else
ws.Activate
Call Formatting
End Select
Next ws

 

[Rick Rothstein \(MVP - VB\)]

You are getting that error because you have 5 If-Then statements without any
corresponding End If statements to complete the blocks. The "Next without
For" error is bogus... it has to do with the way VB manages blocks of code
(irrelevant error messages like this is something you will have to get use
to). Anyway, your code should work if you add 5 individual End If statements
right after the Call Formatting statement.

Rick

 

[Guest]

Thanks guys.

 

[Guest]

one other error you had and an omitted underscore/continuation character on
my part:

I added the ws.Activate because I assume your formatting macro works on the
activesheet since you don't pass an argument to it.

Set wb = ActiveWorkbook
Set sh = ActiveSheet
For Each ws In wb.Worksheets '<-- add Worksheets
If ws.Name <> "SUMMARY" and _
ws.Name <> "Parameters" and _
ws.Name <> "PIVOTDATA" and _
ws.Name <> "PIV_Deliverables" and _
ws.Name <> "PIV_RC" Then
ws.Activate
Call Formatting
Next ws

Application.Goto "Summary_Home"

 

[Guest]

It's all good. Thanks Rick.