worksheet loop

[scott]

I am trying to add subtotals to for each worksheet in a workbook, but it
only runs once. I tried to call the sub routine rather than putting
the code in, but that didn't work either. i.e.
Sub workbookformat()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
call addsubtotals
next sks
end sub

Here is the more detailed code.

Sub workbookformat()
Dim wks As Worksheet
Dim startcell As Range
Dim lastrow As Integer
Dim colcount As Integer


For Each wks In ThisWorkbook.Worksheets

lastrow = ActiveSheet.Cells(65000, "a").End(xlUp).Row
lastcol = ActiveSheet.Cells(1, 255).End(xlToLeft).Column

colcount = lastcol - 15 'Total columns -15 columns of cust info
Set startcell = Cells(lastrow + 1, 15)

startcell.Select

For i = 1 To colcount
Selection.Offset(0, 1).Select
With Selection
.Formula = "=sum(" & Range(Selection.Offset(-1, 0),
Selection.End(xlUp).End(xlUp)).Address(False, False) & ")"
End With

'Add top and bottom borders
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
'.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Next i

Next wks

End Sub

Anyhelp would be appreciated.

Scott

 

[Ron de Bruin]

Try this with a select line

For Each wks In ThisWorkbook.Worksheets
wks.Select

 

[scott]

It errored out and highlighted the wks.select line

 

[Ron de Bruin]

Hi Scott

Do you have hidden worksheets in the workbook??

 

[scott]

No. I have tried running it in different workbooks, but I get the same
error.

 

[scott]

I think it is working with:

For Each wks In Worksheets
wks.Select

Thanks for you help.

 

[Ron de Bruin]

For Each wks In Worksheets

This is the same as
For Each wks In ActiveWorkbook.Worksheets

Thisworkbook will use the sheets in the workbook where the code is in.

 

[scott]

That's what I get for not understanding the code that I am copying. The
code is in my personal workbook.

Thanks again.

 

[Tom Ogilvy]

Thisworkbook.Activate
for each wks in ThisWorkbook.worksheets
wks.Select
Next

the wks can not be selected if the ThisWorkbook is not the activeworkbook.