Exclude sheets from code

  • Thread starter Thread starter Homer
  • Start date Start date
H

Homer

I have a bit of code thats automatically sets borders for new sheets.

When the code runs, it changes the borders on all sheets in the workbook. I
would like to have two sheets excluded from this code.

The sheet names are "All" and "Tally"

Here is my code:

Sub Set_Borders()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
With sht.Range("A1:An53")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlMedium
.Borders.ColorIndex = xlAutomatic
.Borders(xlInsideVertical).Weight = xlHairline
.Borders(xlInsideHorizontal).Weight = xlHairline
End With
With sht.Range("A1:An1")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlMedium
.Borders.ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlInsideVertical).Weight = xlHairline
.Borders(xlInsideHorizontal).Weight = xlHairline
End With
Next sht
Sheets("All").Activate
End Sub
 
You can use if then but I prefer select case for tis kind of thing...

Sub Set_Borders()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
select case sht.name
case "All", "Tally"
case else
With sht.Range("A1:An53")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlMedium
.Borders.ColorIndex = xlAutomatic
.Borders(xlInsideVertical).Weight = xlHairline
.Borders(xlInsideHorizontal).Weight = xlHairline
End With
With sht.Range("A1:An1")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlMedium
.Borders.ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlInsideVertical).Weight = xlHairline
.Borders(xlInsideHorizontal).Weight = xlHairline
End With
end select
Next sht
Sheets("All").Activate
End Sub
 
try this:

Option Explicit

Sub Set_Borders()

Dim sht As Worksheet

For Each sht In ActiveWorkbook.Sheets
If sht.Name = "All" Or sht.Name = "Tally" Then
'do nothing
Else
With sht.Range("A1:An53")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlMedium
.Borders.ColorIndex = xlAutomatic
.Borders(xlInsideVertical).Weight = xlHairline
.Borders(xlInsideHorizontal).Weight = xlHairline
End With

With sht.Range("A1:An1")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlMedium
.Borders.ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlInsideVertical).Weight = xlHairline
.Borders(xlInsideHorizontal).Weight = xlHairline
End With
End If
Next sht
Sheets("All").Activate
End Sub

worked for me.
:)
susan
 
Thanks Jim. Code now works as needed.

Don

Jim Thomlinson said:
You can use if then but I prefer select case for tis kind of thing...

Sub Set_Borders()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
select case sht.name
case "All", "Tally"
case else
With sht.Range("A1:An53")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlMedium
.Borders.ColorIndex = xlAutomatic
.Borders(xlInsideVertical).Weight = xlHairline
.Borders(xlInsideHorizontal).Weight = xlHairline
End With
With sht.Range("A1:An1")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlMedium
.Borders.ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlInsideVertical).Weight = xlHairline
.Borders(xlInsideHorizontal).Weight = xlHairline
End With
end select
Next sht
Sheets("All").Activate
End Sub
Click to expand...
 
Back
Top