I had an error in the code.
The workbook has to be activated before the worksheet can be selected:

Dim wb As Workbook
Dim wsheet As Worksheet 'not just an object

For Each wb In Application.Workbooks
wb.activate 'added
Set wsheet = wb.Worksheets("sheet1")
With wsheet
.select 'if you want to select a cell, you have to select the sheet
.Columns("A:AG").Columns.AutoFit
.range("a2").select
End With
wb.save
wb.close savechanges:=false
Next wb

Elaine J. wrote:

Thanks for your response. I tried your code and I got a "runtime error €˜1004
method €˜select of object_worksheet failed. Was I supposed to add
something after .select??

Thanks so much for your help.

Elaine

"Dave Peterson" wrote:

I don't think your code does what you want.

I'd be more careful with what belongs to what--what worksheet belongs to what
workbook and what range belongs to what worksheet.

Dim wb As Workbook
Dim wsheet As Worksheet 'not just an object

For Each wb In Application.Workbooks
Set wsheet = wb.Worksheets("sheet1")
With wsheet
.select 'if you want to select a cell, you have to select the sheet
.Columns("A:AG").Columns.AutoFit
.range("a2").select
End With
wb.save
wb.close savechanges:=false
Next wb

Elaine J. wrote:

I recently posted a question regarding setting up a For each workbook
statement. It it basically working, but I have a follow up question.

1. I have 17 workbooks open at one time. I am attempting to go through
each workbook and format the columns to autofit. The code works for 9 of the
documents and then stops. This is the code that I am using.

Dim wb As Workbook
Dim wsheet As Object

For Each wb In Application.Workbooks
Set wsheet = Worksheets("sheet1")

With wsheet

Columns("A:AG").Select
Selection.Columns.AutoFit
ActiveCell.Offset(1, 0).Select

End With

ActiveWorkbook.Save
ActiveWorkbook.Close

Next wb

2. I have noticed that the nine workbooks that work are the 9 that show up
in the drop down list under the Windows menu. (I'm not sure that has
anything to do with it, but that is the way it appears to me.)

So my question is: Is there a limit on how many workbooks you can have open
and use the For Each method? And if so, how do I get around it? Or is there
something wrong with my code?

Thanks,

Elaine

--

Dave Peterson


--

Dave Peterson