For Each ws In ThisWorkbook.Worksheets question

[thewaksta]

Hi,

I'm very new to VBA and have a problem which I'm sure must be very simple to solve but I have no idea how to go about this.

I have a workbook with three permanent worksheets (Summary, Employees, Project Rules). I want to add new worksheets to this workbook and format them without touching the permanent sheets. I would like to tell VBA to format every worksheet that is not named Summary, Employees or Project Rules (or alternatively all worksheets except for Sheet1, Sheet2 or Sheet3). How can I do this?

Thanks
thewaksta

 

[Mindpsyche]

Something like this:

Option Explicit


Sub Wksht()
    Dim ws As Worksheet
    For Each ws In Worksheets
    If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" And ws.Name <> "Sheet3" Then
        ws.Range("A1").Interior.ColorIndex = 3
    End If
    Next ws
    
End Sub

 

[HaHoBe]

Hi, thewaksta ,

I´d use a Select Case construct over Ifs:

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  Select Case ws.Name
    Case "Summary", "Employees", "Project Rules"
      'do nothing
    Case Else
      'do your formatting
  End Select
Next ws

Ciao,
Holger

 

[thewaksta]

Thanks,

I went with something more complicated (for me anyway) which worked but might try these tomorrow. I went with:

Code:
j = 0

For Each ws In ThisWorkbook.Worksheets
If ws.Visible = True Then
j = j + 1
End If
Next ws

For i = 4 To j

Sheets(i).Activate
Formatting.....

Next i

 

[HaHoBe]

Hi, thewaksta,

if all worksheets inn the workbook are visible there´s no need to loop through them as Worksheet.Count will deliver the result.

Please mind that using the index may cause problems as the procedure will not work on the three left worksheets regardless of their name. So if anybody will shift a worksheet to the left at any of the first three positions...

There´s no need to activate a worksheet for formatting, Mindpsyche gave an example of how to start with:

ws.Range("A1").Interior.ColorIndex = 3

which will color the range A1 of any given sheet in red (except for those which need not be formatted).

If there´s more than one action to be taken you may go with a With ws... End With like

With ws
  With .UsedRange.Rows(1).Font
    .Name = "Arial"
    .Size = 14
    .Bold = True
    .Color = vbGreen
  End With
End With

which will bold the first line of the used range, set the font to Arial and size to 14 and color it green.

Ciao,
Holger

 

[Michael M]

Thanks,

I went with something more complicated (for me anyway) which worked but might try these tomorrow. I went with:

Code:
j = 0

For Each ws In ThisWorkbook.Worksheets
If ws.Visible = True Then
j = j + 1
End If
Next ws

For i = 4 To j

Sheets(i).Activate
Formatting.....

Next i

While this will work....and generally that's all that counts.....it could be incredibly slow if you added a heap of new sheets.
You are doing the looping process twice over, which is a redundant process.
I think you'll find the other preocesses much quicker and efficient

 

[drsarao]

Hi,

I'm very new to VBA and have a problem which I'm sure must be very simple to solve but I have no idea how to go about this.

I have a workbook with three permanent worksheets (Summary, Employees, Project Rules). I want to add new worksheets to this workbook and format them without touching the permanent sheets. I would like to tell VBA to format every worksheet that is not named Summary, Employees or Project Rules (or alternatively all worksheets except for Sheet1, Sheet2 or Sheet3). How can I do this?

Thanks
thewaksta

Adding the worksheet thru code and formatting the sheet beforehand is also a possibility.

To me formatting all the sheets (even though .UsedRange only) (again and again when a new sheet is added) seems a bit of overkill.

Thus:

Sub AddNew()
With ActiveWorkbook.Worksheets.Add
    With .Range("A1:Z30").Font   '.Cells.Font if you want global format
        .Name = "Arial"
        .Size = 14
    End With
End With
End Sub

 

[przemek]

Hi

I'm looking for soem help with looping thorugh Workbook worksheets. Below code works ok in first active worksheet (AX1) but dosen't loop throught any othere worksheets.
Not sure where the problem is.
Dim WS As Worksheet
Dim x As Integer
Dim NumRows As Integer


For Each WS In Worksheets
NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count ' each worksheet got diffrent nr of rows
Row = 2
Select Case Range("K2").Text
Case "AX1"
For x = 1 To NumRows
If Cells(Row, 8).Value > 1 Then
Cells(Row, 9) = (Cells(Row, 6).Value / Cells(Row, 7).Value) * 2.25
Else
Cells(Row, 9) = Cells(Row, 6).Value / Cells(Row, 7).Value
End If
Row = Row + 1
Next x

Case "XY1"
For x = 1 To NumRows
If Cells(Row, 8).Value > 1 Then
Cells(Row, 9) = (Cells(Row, 6).Value / Cells(Row, 7).Value) * 1.25
Else
Cells(Row, 9) = Cells(Row, 6).Value / Cells(Row, 7).Value
End If
Row = Row + 1
Next x

Case Else
End Select
Next WS
End Sub


Thanks

 

[Mindpsyche]

If AX1 and YX1 are the Worksheets, why are you repeating the Loop?

If your sheets are named as AX1 etc...

Try something like:

For Each WS In Worksheets
        If WS.Name = "AX1" Then
        MsgBox "AX1" 'Replace with whatever you need to be done if the Sheet is AX1
        End If
    Next WS

Or you can also use Select Case Statement:

For Each WS In Worksheets
    
        Select Case WS.Name
    
            Case "AX1"
            MsgBox "AX1"
            
            Case "XY1"
            MsgBox "XY1"
            
        End Select
    
    Next WS

 

[njimack]

You need to qualify your references. So this:

For Each WS In Worksheets
NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count ' each worksheet got diffrent nr of rows
Row = 2
Select Case Range("K2").Text
Case "AX1"
For x = 1 To NumRows
If Cells(Row, 8).Value > 1 Then
Cells(Row, 9) = (Cells(Row, 6).Value / Cells(Row, 7).Value) * 2.25
Else
Cells(Row, 9) = Cells(Row, 6).Value / Cells(Row, 7).Value
End If
Row = Row + 1
Next x

should be this:

For Each ws In Worksheets
NumRows = [COLOR=#ff0000][B].[/B][/COLOR]Range("A2", [COLOR=#ff0000][B].[/B][/COLOR]Range("A2").End(xlDown)).Rows.Count ' each worksheet got diffrent nr of rows
Row = 2
Select Case [COLOR=#ff0000][B].[/B][/COLOR]Range("K2").Text
Case "AX1"
For x = 1 To NumRows
If [COLOR=#ff0000][B].[/B][/COLOR]Cells(Row, 8).Value > 1 Then
[COLOR=#ff0000][B].[/B][/COLOR]Cells(Row, 9) = ([COLOR=#ff0000][B].[/B][/COLOR]Cells(Row, 6).Value / [COLOR=#ff0000][B].[/B][/COLOR]Cells(Row, 7).Value) * 2.25
Else
[COLOR=#ff0000][B].[/B][/COLOR]Cells(Row, 9) = [B].[COLOR=#ff0000][/COLOR][/B]Cells(Row, 6).Value / [COLOR=#ff0000][B].[/B][/COLOR]Cells(Row, 7).Value
End If
Row = Row + 1
Next x