VBA applies to selected worksheets

[Apple08]

Hi All

I have a macro which is applied to all worksheets except 'configuration' or 'project managers' worksheets. However, when I run it, it seems the macro also affect these two worksheets. Please could someone help me to resolve this? Many thanks.

The macro starts with:

Sub report()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In Worksheets
If sh.Name <> "configuration" Or sh.Name <> "project managers" Then
    sh.Activate

 

[vcoolio]

Hello Apple08,

Try changing the "Or" to "And" and remove the "sh.Activate" part.

I hope that this helps.

Cheerio,
vcoolio.

 

[Fluff]

Should be And not or

Sub report()
    Dim sh As Worksheet
    Application.ScreenUpdating = False
    For Each sh In Worksheets
        If sh.Name <> "details" And sh.Name <> "raid log" Then
            sh.Activate
        End If
    Next sh
End Sub

Also make sure your sheet names are correct, the code is case sensitive, so Configuration <> configuration

 

[alansidman]

Change your "or" to "and"

It should work then.

 

[Apple08]

Thank you All! I have changed 'Or' to 'And' and it works perfectly now! Thousand thanks to all of you!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Apple08]

You guys are so helpful, I appreciate it a lot!

 

[Teeks2k]

Should be And not or

Sub report()
    Dim sh As Worksheet
    Application.ScreenUpdating = False
    For Each sh In Worksheets
        If sh.Name <> "details" And sh.Name <> "raid log" Then
            sh.Activate
        End If
    Next sh
End Sub

Also make sure your sheet names are correct, the code is case sensitive, so Configuration <> configuration

To get around this you could set the case before hand

Sub report()
Dim wb as Workbook
Dim sh As Worksheet
Dim x as long

Application.ScreenUpdating = False

Set wb = ThisWorkBook

For x = 1 to wb.sheets.count
    'assign temporary sheet name
     wstmp = UCase(wb.Sheets.Name)
        If (InStr(wstmp, "CONFIGURATION") = 0 And (InStr(wstmp, "PROJECT MANAGERS") = 0 Then
            sh.Activate
        End If
Next x

 

[Fluff]

Even easier

If Ucase(sh.Name) <> "CONFIGURATION" Or sh.Name <> "PROJECT MANAGERS" Then

 

[Greg Truby]

Just a quick "drive by" post here with some overall thoughts on your architecture:

• You're already looping the collection of worksheets. Why are you activating them? Are you performing some action that requires activation of the sheet (like turning off gridlines)? Or could whatever-you're-doing be accomplished simply by referencing the Sh object itself?
• You're already under the hood in the VBE. If the sheets you wish to skip are permanent, i.e. they aren't created dynamically using VBA, then it's a simple matter to edit these worksheets' codenames. Why not key off their codenames instead of their names? Codenames are much less prone to change than regular names. [If they are created dynamically then changing the codenames is still possible, but it's a bit more complicated.] And, since the names would only be changed by another coder, you'll know the case from the get go and can nix the need to bake case handling into your tests.
• When I'm writing an IF statement that is running several tests, I like break up the lines so the tests stack vertically. For me it makes the code a little easier to read, especially if the statement begins to contain three or more tests; purely a personal preference:

  ...
      If sh.CodeName <> "Configuration" _
      And sh.CodeName <> "ProjectManagers" Then
          With sh
               ... code that does stuff on sheet sh ...
          End With
      End If