For next question

[Faboboren]

Hi,

I have this code that I want to run in 15 sheets. I do not want to run where
I have my macros. It is not giving me error, but still applying borders in
Macro sheet. Please any idea to solve that?

Sub Set_Borders()
Dim sht As Worksheet

For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Macro.xls" Then
With sht.Range("A6:C6")
.BorderAround LineStyle:=xlSolid, Weight:=xlMedium,
ColorIndex:=xlAutomatic
End With
With sht.Range("A7:C11")
.BorderAround LineStyle:=xlSolid, Weight:=xlMedium,
ColorIndex:=xlAutomatic
End With
With sht.Range("A12:C16")
.BorderAround LineStyle:=xlSolid, Weight:=xlMedium,
ColorIndex:=xlAutomatic
End With
With sht.Range("A17:C21")
.BorderAround LineStyle:=xlSolid, Weight:=xlMedium,
ColorIndex:=xlAutomatic
End With
End If
Next sht

End Sub

 

[Mike H]

Hi,

Your code shouldn'y have applied the formatting to the sheet called
macro.xls and if it did then check the spelling is the same as in the code.
I'd change your code slightly to this

Sub Set_Borders()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
If sht.Name <> "Macro.xls" Then
With sht
.Range("A6:C6").BorderAround LineStyle:=xlSolid, Weight:=xlMedium,
ColorIndex:=xlAutomatic
.Range("A7:C11").BorderAround LineStyle:=xlSolid, Weight:=xlMedium,
ColorIndex:=xlAutomatic
.Range("A12:C16").BorderAround LineStyle:=xlSolid, Weight:=xlMedium,
ColorIndex:=xlAutomatic
.Range("A17:C21").BorderAround LineStyle:=xlSolid, Weight:=xlMedium,
ColorIndex:=xlAutomatic
End With
End If
Next sht
End Sub


Mike

 

[Joel]

The sheet name on the tab is probably not capitalize the same as in your
macro OR there are extra spaces in the sheet name thsi should help

from:
If sht.Name <> "Macro.xls" Then

to:
If Ucase(trim(sht.Name)) <> MACRO.XLS" Then

 

[Faboboren]

Thanks Mike and Joel,

I have checked what you said, and still doing the borders in Macro sheet...

 

[JLGWhiz]

Do you actually have a sheet named "Macro.xls" or is it just named "Macro"?
If there is any mismatch at all in the sheet name, then the format code will
apply to that sheet as well. The sheet name must me the same in case and
content for the <> operator to exclude that sheet.

 

[Ken]

Is the .xls really part of the sheet name?

If it really matches, then I don't know what you problem could be. To
get around the exact match issue, and at the same time to allow you to
rename or reposition the worksheet, you could use

If sh.Name <> Sheet1.Name Then

where Sheet1 would be replaced by the appropriate sheet that you have
named "macro.xls"; you can see what sheet number to use in the VBA
project explorer

Good luck.

Ken
Norfolk, Va

 

[Faboboren]

JLGWhiz & Ken,

I deleted xls and now is working ....

Thanks