Exclude more than one worksheet

[AlAbba]

Hi everyone,
I'm new to this forum, and I hope I can get help here.
I'm working on an Excel macro that selects all the worksheets in a workbook exept a summary sheet. hoe can I exclude other worksheets in addition to summary? The code starts like this:
For i = 1 To Sheets.Count
Sheets(i).Select
If Not Sheets(i).Name = "Summary" Then
Range("A3").Select
I tried things like:
"summary, sheet2, sheet3" - and "(summary),(sheet2),(sheet3)". Nothing works. Can anyone help me out?
Thanks!

 

[TonyJollans]

Hi AlAbba,

You can't do that with an If, but if I understand, what you want is something along the lines of:

[blue]    Select Case Sheets(i).Name
        Case "Sheet1", "Sheet2"
        Case Else
            [green]' Do your stuff here[/green]
    End Select[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[AlAbba]

Hi Tony,
Thank you for your prompt help. I hope I'm replying correctly! Hoever, I don't want to use select because all the sheets could be changed exept 5 or 6 sheets, and I want to exclude those sheets from the main bulk of the worksheets.
Thanks

 

[TonyJollans]

Hi AlAbba,

I think we may have some confusion here. The Select Case statement in VBA is like a complex If statement - it has nothing to do with the Excel Select

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[AlAbba]

Hi Tony,
You are right, but then how can I select all the worksheets, that could have different names, exept those 5 or 6 sheets.
Please notice that I'm not a programmer, and the purpose of this macro is to format all the worksheet exept those 5 or 6 sheets.
Many thanks for your help!

 

[xlbo]

Well, you will either need commonality in the names of the sheets to exclude or those to include then

Select Case is definitely the way to go but unless you can specify sheet names it isn't going to be easy - This might give you a better idea of what Tony is suggesting:

for each sht in thisworkbook.worksheets
 select case sht.name
  case "Summary", "AnotherSheet", "SomeSheet"
    'do not apply formatting
  case else
    'formatting code goes here
 end select
next

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[AlAbba]

Hi xlbo,
Thank you for yor help. I have tried your code, but looks like there something wrong with my macro. It works without error, but it dosen't select the sheets. Here is the whole macro. Can you find out where is the mistake?

Thanks

 

[AlAbba]

Sorry xlbo, I didn't past the code.. Here it is!


Sub FormatWSs()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Worksheets
Select Case sht.Name
Case "summary", "ePortfolio_IDAs", "E255", "ePortfolio_STMs"

Cells.Select

With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With

Columns("A").Select
Selection.ColumnWidth = 15
Columns("B:AQ").Select
Selection.ColumnWidth = 20

Rows("1:4").Select
Selection.RowHeight = 15
Selection.Font.Bold = True

Rows("3:4").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Rows("5:16").Select
Selection.RowHeight = 55


Case Else

Cells.Select

'formatting code goes here

Range("A3").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("B17:H17").Select
Range("H17").Activate
Selection.NumberFormat = "[h]:mm"

Cells.Select

With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With

Columns("A").Select
Selection.ColumnWidth = 14
Columns("B:G").Select
Selection.ColumnWidth = 11
Columns("H").Select
Selection.ColumnWidth = 5
Rows("1:4").Select
Selection.RowHeight = 15
Selection.Font.Bold = True

Rows("3:4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Rows("5:16").Select
Selection.RowHeight = 35
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""Available"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 41
End With
Selection.FormatConditions(1).Interior.ColorIndex = 2
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""Not Available"""
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
With Selection.FormatConditions(2).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(2).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(2).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(2).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

End Select
Next
Application.ScreenUpdating = True
End Sub

 

[xlbo]

you need to change:
Cells.Select
to
sht.Cells.Select

You will need to apply this to all lines OUTSIDE of your WITH statements so

sht.Rows("5:16").Select
Selection.RowHeight = 35

BUT - you would be wise to read Skipvought's FAQ on why SELECTING in vba is usually unnecessary - you could change the above to:

sht.Rows("5:16").RowHeight = 35


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[AlAbba]

Ok I will change the code and try it!
Thanks xlbo!