Else If Question

[Guzzlr]

Hello All

For Each sht In ActiveWorkbook.Worksheets
    If sht.Visible And (sht.Name = "Alignment") Then
    sht.Delete
    Sheets.Add.Name = "Alignment"
    sht.Activate
    Cells.Select
        With Selection
            .Clear
            .RowHeight = 20.01
            .ColumnWidth = 8.43
            Columns("B:C").ColumnWidth = 19.01
            .NumberFormat = "0.00"
            .Font.Name = "Calibri"
            .Font.Size = 12
            .Interior.Pattern = xlNone
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            Range("B2") = "MES Document"
            Range("B2").Interior.ColorIndex = 4
'            Range("D6") = "Not Used"
'            Range("D6").Interior.Color = RGB(255, 157, 91)
            Range("A1").Select
        End With
    End If
Next sht

This code will add and format a new "Alignment" sheet correctly. But what if there is no "Alignment" sheet already in the workbook? This is where I'm having problems using an ElseIf statement, it seems it wants to keep adding multiple worksheets...
I got irritated and deleted the Else part of it.
Sorry

 

[Joe4]

Don't it do it that way! What that will do is try to add a new sheet named "Alignment" the number of times as the number of sheets you have NOT named "Alignment".
You do not need a loop to see if a sheet named "Alignment" exists.
See here: https://www.mrexcel.com/forum/excel-questions/985143-vba-check-if-sheet-exists.html

 

[Fluff]

Try this

For Each Sht In ActiveWorkbook.Worksheets
    If Sht.Visible And (Sht.Name = "Alignment") Then
      Sht.Delete
      Exit For
    End If
Next Sht

Sheets.Add.Name = "Alignment"
Cells.Select
With Selection
    .Clear
    .RowHeight = 20.01
    .ColumnWidth = 8.43
    Columns("B:C").ColumnWidth = 19.01
    .NumberFormat = "0.00"
    .Font.Name = "Calibri"
    .Font.size = 12
    .Interior.Pattern = xlNone
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    Range("B2") = "MES Document"
    Range("B2").Interior.ColorIndex = 4
'            Range("D6") = "Not Used"
'            Range("D6").Interior.Color = RGB(255, 157, 91)
    Range("A1").Select
End With

 

[Guzzlr]

Don't it do it that way! What that will do is try to add a new sheet named "Alignment" the number of times as the number of sheets you have NOT named "Alignment".

That is exactly what it was doing, and why I was getting really irritated!

 

[Guzzlr]

Try this

For Each Sht In ActiveWorkbook.Worksheets
    If Sht.Visible And (Sht.Name = "Alignment") Then
      Sht.Delete
      Exit For
    End If
Next Sht

Sheets.Add.Name = "Alignment"
Cells.Select
With Selection
    .Clear
    .RowHeight = 20.01
    .ColumnWidth = 8.43
    Columns("B:C").ColumnWidth = 19.01
    .NumberFormat = "0.00"
    .Font.Name = "Calibri"
    .Font.size = 12
    .Interior.Pattern = xlNone
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    Range("B2") = "MES Document"
    Range("B2").Interior.ColorIndex = 4
'            Range("D6") = "Not Used"
'            Range("D6").Interior.Color = RGB(255, 157, 91)
    Range("A1").Select
End With

Ohh...I didn't think to do it this way...I'll try it later and report back.

 

[Guzzlr]

Try this

For Each Sht In ActiveWorkbook.Worksheets
    If Sht.Visible And (Sht.Name = "Alignment") Then
      Sht.Delete
      Exit For
    End If
Next Sht

Sheets.Add.Name = "Alignment"
Cells.Select
With Selection
    .Clear
    .RowHeight = 20.01
    .ColumnWidth = 8.43
    Columns("B:C").ColumnWidth = 19.01
    .NumberFormat = "0.00"
    .Font.Name = "Calibri"
    .Font.size = 12
    .Interior.Pattern = xlNone
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    Range("B2") = "MES Document"
    Range("B2").Interior.ColorIndex = 4
'            Range("D6") = "Not Used"
'            Range("D6").Interior.Color = RGB(255, 157, 91)
    Range("A1").Select
End With

This is working...Thank you!

 

[Fluff]

Glad to help & thanks for the feedback

Though as Joe4 pointed out, you can get rid of the loop & replace it with a function.