I want to exclude the last 3 worksheets from the following macro. Can you
please show how the code should be adjusted (and cleaned up). Many thanks.

Sub Sheetsless3()
Dim wSht As Worksheet
Dim myRng As Range
Dim allwShts As Sheets
Set allwShts = Worksheets
For Each wSht In allwShts
wSht.Columns(1).ColumnWidth = 51.57
wSht.Columns(2).ColumnWidth = 8
Set myRng = wSht.Range("C4:N4")
With myRng
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
End With
Next wSht
End Sub

Try this

Dim a As Integer
For a = 1 To ThisWorkbook.Sheets.Count - 3
MsgBox Sheets(a).Name
Next a


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Lambtwo" wrote in message news:MN3Fg.423034$IK3.200308@pd7tw1no...
I want to exclude the last 3 worksheets from the following macro. Can you please show how the code should be adjusted (and cleaned
up). Many thanks.

Sub Sheetsless3()
Dim wSht As Worksheet
Dim myRng As Range
Dim allwShts As Sheets
Set allwShts = Worksheets
For Each wSht In allwShts
wSht.Columns(1).ColumnWidth = 51.57
wSht.Columns(2).ColumnWidth = 8
Set myRng = wSht.Range("C4:N4")
With myRng
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
End With
Next wSht
End Sub

Sheets(1).Select

Dim a As Integer
For a = 1 To (ActiveWorkbook.Sheets.Count - 3)
With ActiveSheet
.Columns(1).ColumnWidth = 51.57
.Columns(2).ColumnWidth = 8
.Range("C4:N4").HorizontalAlignment = xlRight
.Range("C4:N4").VerticalAlignment = xlTop
End With
ActiveSheet.Next.Select
Next a


Lambtwo wrote:
I want to exclude the last 3 worksheets from the following macro. Can you
please show how the code should be adjusted (and cleaned up). Many thanks.

Sub Sheetsless3()
Dim wSht As Worksheet
Dim myRng As Range
Dim allwShts As Sheets
Set allwShts = Worksheets
For Each wSht In allwShts
wSht.Columns(1).ColumnWidth = 51.57
wSht.Columns(2).ColumnWidth = 8
Set myRng = wSht.Range("C4:N4")
With myRng
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
End With
Next wSht
End Sub

When I copied your code in following the Sub line, it still goes through all
worksheets.

Could you show where each line of your code should be placed?

Thanks

"Ron de Bruin" wrote in message
...
Try this

Dim a As Integer
For a = 1 To ThisWorkbook.Sheets.Count - 3
MsgBox Sheets(a).Name
Next a


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Lambtwo" wrote in message
news:MN3Fg.423034$IK3.200308@pd7tw1no...
I want to exclude the last 3 worksheets from the following macro. Can you
please show how the code should be adjusted (and cleaned up). Many thanks.

Sub Sheetsless3()
Dim wSht As Worksheet
Dim myRng As Range
Dim allwShts As Sheets
Set allwShts = Worksheets
For Each wSht In allwShts
wSht.Columns(1).ColumnWidth = 51.57
wSht.Columns(2).ColumnWidth = 8
Set myRng = wSht.Range("C4:N4")
With myRng
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
End With
Next wSht
End Sub

Use this

Sub Sheetsless3()
Dim a As Integer
For a = 1 To ThisWorkbook.Sheets.Count - 3
Sheets(a).Columns(1).ColumnWidth = 51.57
Sheets(a).Columns(2).ColumnWidth = 8
With Sheets(a).Range("C4:N4")
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
End With
Next a
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Lambtwo" wrote in message news:Ab4Fg.412228$iF6.34883@pd7tw2no...
When I copied your code in following the Sub line, it still goes through all worksheets.

Could you show where each line of your code should be placed?

Thanks

"Ron de Bruin" wrote in message ...
Try this

Dim a As Integer
For a = 1 To ThisWorkbook.Sheets.Count - 3
MsgBox Sheets(a).Name
Next a


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Lambtwo" wrote in message news:MN3Fg.423034$IK3.200308@pd7tw1no...
I want to exclude the last 3 worksheets from the following macro. Can you please show how the code should be adjusted (and
cleaned up). Many thanks.

Sub Sheetsless3()
Dim wSht As Worksheet
Dim myRng As Range
Dim allwShts As Sheets
Set allwShts = Worksheets
For Each wSht In allwShts
wSht.Columns(1).ColumnWidth = 51.57
wSht.Columns(2).ColumnWidth = 8
Set myRng = wSht.Range("C4:N4")
With myRng
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
End With
Next wSht
End Sub

That's odd about it going through all sheets. I just placed the code
in a Module exactly as it's shown above. Like this:

Sub FormatSheets()
Sheets(1).Select

Dim a As Integer
For a = 1 To (ActiveWorkbook.Sheets.Count - 3)
With ActiveSheet
.Columns(1).ColumnWidth = 51.57
.Columns(2).ColumnWidth = 8
.Range("C4:N4").HorizontalAlignment = xlRight
.Range("C4:N4").VerticalAlignment = xlTop
End With
ActiveSheet.Next.Select
Next a

End Sub

It should start by selecting the first sheet regardless of name, and
loop through until it gets to the third to the last sheet.
Lambtwo wrote:
When I copied your code in following the Sub line, it still goes through all
worksheets.

Could you show where each line of your code should be placed?

Thanks

"Ron de Bruin" wrote in message
...
Try this

Dim a As Integer
For a = 1 To ThisWorkbook.Sheets.Count - 3
MsgBox Sheets(a).Name
Next a


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Lambtwo" wrote in message
news:MN3Fg.423034$IK3.200308@pd7tw1no...
I want to exclude the last 3 worksheets from the following macro. Can you
please show how the code should be adjusted (and cleaned up). Many thanks.

Sub Sheetsless3()
Dim wSht As Worksheet
Dim myRng As Range
Dim allwShts As Sheets
Set allwShts = Worksheets
For Each wSht In allwShts
wSht.Columns(1).ColumnWidth = 51.57
wSht.Columns(2).ColumnWidth = 8
Set myRng = wSht.Range("C4:N4")
With myRng
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
End With
Next wSht
End Sub

Thanks very much to both of you, this worked ..... appreciated

"Ron de Bruin" wrote in message
...
Use this

Sub Sheetsless3()
Dim a As Integer
For a = 1 To ThisWorkbook.Sheets.Count - 3
Sheets(a).Columns(1).ColumnWidth = 51.57
Sheets(a).Columns(2).ColumnWidth = 8
With Sheets(a).Range("C4:N4")
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
End With
Next a
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Lambtwo" wrote in message
news:Ab4Fg.412228$iF6.34883@pd7tw2no...
When I copied your code in following the Sub line, it still goes through
all worksheets.

Could you show where each line of your code should be placed?

Thanks

"Ron de Bruin" wrote in message
...
Try this

Dim a As Integer
For a = 1 To ThisWorkbook.Sheets.Count - 3
MsgBox Sheets(a).Name
Next a


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Lambtwo" wrote in message
news:MN3Fg.423034$IK3.200308@pd7tw1no...
I want to exclude the last 3 worksheets from the following macro. Can
you please show how the code should be adjusted (and cleaned up). Many
thanks.

Sub Sheetsless3()
Dim wSht As Worksheet
Dim myRng As Range
Dim allwShts As Sheets
Set allwShts = Worksheets
For Each wSht In allwShts
wSht.Columns(1).ColumnWidth = 51.57
wSht.Columns(2).ColumnWidth = 8
Set myRng = wSht.Range("C4:N4")
With myRng
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
End With
Next wSht
End Sub