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

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

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

Gary,

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

davi

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=53253

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

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

Thanks Tim. As simple as that!

Davi

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=53253