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
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

--


Gary


"davidm" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
> http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=532532
>

[davidm]

Gary,

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

david

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
> http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=532532
>

[davidm]

Thanks Tim. As simple as that!

David