Looping thru Workbooks problem

[davidm]

Can someone explain why this code generates "Object doesn't support this
property or method" error?

Sub LoopThruWkBooksWkSheets()
Dim wb as WorkBook
Dim ws as WorkSheet

For Each wb In Workbooks
For Each ws In Worksheets
wb.ws.Cells.ColumnWidth = 3 ----> error line
Next
Next
End Sub

The idea is to loop through all open workbooks and all worksheets
therein to reset the columnwidths to 3. The above code can be tweaked
to work by activating each Workbook as in:

Sub LoopThruWkBooksWkSheets()
Dim wb as WorkBook
Dim ws as WorkSheet

For Each wb In Workbooks
wb.activate
For Each ws In Worksheets
ws.Cells.ColumnWidth = 3 ----> error line
Next
Next
End Sub

...but this is arguably not elegant even by turning off
screenupdating.


TIA

David

 

[Gary Keramidas]

use something like this

Option Explicit
Sub LoopThruWkBooksWkSheets()
Dim wb As Workbook
Dim ws As Worksheet

For Each wb In Workbooks
For Each ws In Worksheets
With ws
..Cells.ColumnWidth = 3 ' ----> error line
End With
Next
Next
End Sub

 

[davidm]

Gary,

Your code works on only the ActiveWorkBook. Not surprising, considerin
the fact that the other workbooks are not looped.

davi

 

[Tim Williams]

You should qualify the Worksheets reference.

'########################
For Each wb In Workbooks
For Each ws In wb.Worksheets
ws.Cells.ColumnWidth = 3
Next ws
Next wb
'########################


Tim

 

[davidm]

Thanks Tim. As simple as that!

Davi