Looping thru Workbooks problem

  • Thread starter Thread starter davidm
  • Start date Start date
D

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

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

davi
 
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
 
Back
Top