worksheet loop
 

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

worksheet loop
 

Try this with a select line

For Each wks In ThisWorkbook.Worksheets
wks.Select



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"scott" wrote in message ...
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

worksheet loop
 

It errored out and highlighted the wks.select line


Ron de Bruin wrote:

Try this with a select line

For Each wks In ThisWorkbook.Worksheets
wks.Select

worksheet loop
 

Hi Scott

Do you have hidden worksheets in the workbook??

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"scott" wrote in message ...
It errored out and highlighted the wks.select line


Ron de Bruin wrote:

Try this with a select line

For Each wks In ThisWorkbook.Worksheets
wks.Select

worksheet loop
 

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


Ron de Bruin wrote:

Hi Scott

Do you have hidden worksheets in the workbook??

worksheet loop
 

I think it is working with:

For Each wks In Worksheets
wks.Select

Thanks for you help.



scott wrote:

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


Ron de Bruin wrote:

Hi Scott

Do you have hidden worksheets in the workbook??

worksheet loop
 

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.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"scott" wrote in message ...
I think it is working with:

For Each wks In Worksheets
wks.Select

Thanks for you help.



scott wrote:

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


Ron de Bruin wrote:

Hi Scott

Do you have hidden worksheets in the workbook??

worksheet loop
 

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

Thanks again.


Ron de Bruin wrote:

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.

worksheet loop
 

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

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

--
Regards,
Tom Ogilvy

"Ron de Bruin" wrote in message
...
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.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"scott" wrote in message
...
I think it is working with:

For Each wks In Worksheets
wks.Select

Thanks for you help.



scott wrote:

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


Ron de Bruin wrote:

Hi Scott

Do you have hidden worksheets in the workbook??