ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Exclude sheets from code (https://www.excelbanter.com/excel-programming/432148-exclude-sheets-code.html)

homer
Exclude sheets from code
 
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

Jim Thomlinson
Exclude sheets from code
 
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

--
HTH...

Jim Thomlinson


"Homer" wrote:

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

Susan
Exclude sheets from code
 
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





On Aug 7, 12:13*pm, Homer wrote:
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

homer
Exclude sheets from code
 
Thanks Jim. Code now works as needed.

Don

"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"Homer" wrote:

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


All times are GMT +1. The time now is 12:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com