For each Workbook limit?

[Elaine J.]

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

 

[barnabel]

you don't appear to be accessing the wb. I think your macro is autofitting
the same sheet over and over.
try:
Set wsheet = wb.Worksheets("sheet1")

 

[Dave Peterson]

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

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]

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

Dave, I am so sorry. I added the code you suggested (wb.activate), but I am
still getting the same error code.

I have my code in a hidden personal.xls. Does that make any difference? I
am using the code exactly as you suggested (cut and pasted into a sub). Can
you see what I am doing wrong??

Thanks again,

Elaine

Sub format()
'
Dim wb As Workbook
Dim wsheet As Worksheet 'not just an object

For Each wb In Application.Workbooks
wb.Activate

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


End Sub

 

[barnabel]

Do all of the books have a sheet named "Sheet 1"?
You could rewrite it as:
Sub format()
'
Dim wb As Workbook
Dim wsheet As Worksheet 'not just an object

For Each wb In Application.Workbooks
wb.Activate

for each wsheet In wb.Worksheets
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
next
wb.Save
wb.Close savechanges:=False
Next wb


End Sub

Dave, I am so sorry. I added the code you suggested (wb.activate), but I am
still getting the same error code.

I have my code in a hidden personal.xls. Does that make any difference? I
am using the code exactly as you suggested (cut and pasted into a sub). Can
you see what I am doing wrong??

Thanks again,

Elaine

Sub format()
'
Dim wb As Workbook
Dim wsheet As Worksheet 'not just an object

For Each wb In Application.Workbooks
wb.Activate

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


End Sub

 

[Dave Peterson]

Maybe adding some checking would be best (hidden workbooks will cause errors and
workbooks without sheets named Sheet1 will cause errors):

Option Explicit
Sub format2()

Dim WB As Workbook

For Each WB In Application.Workbooks
If IsWorkbookVisible(WB) = False Then
'skip it
Else
If SheetExists("Sheet1", WB) = False Then
'skip it
Else
With WB.Worksheets("sheet1")
.Columns("A:AG").Columns.AutoFit
Application.Goto .Range("a2"), scroll:=True
End With
WB.Save
WB.Close savechanges:=False
End If
End If
Next WB

End Sub

Function IsWorkbookVisible(WB As Workbook) As Boolean
Dim myWin As Window
IsWorkbookVisible = False
For Each myWin In WB.Windows
If myWin.Visible = True Then
IsWorkbookVisible = True
Exit Function
End If
Next myWin
End Function

Function SheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
SheetExists = CBool(Len(WB.Sheets(SheetName).Name) > 0)

Dave, I am so sorry. I added the code you suggested (wb.activate), but I am
still getting the same error code.

I have my code in a hidden personal.xls. Does that make any difference? I
am using the code exactly as you suggested (cut and pasted into a sub). Can
you see what I am doing wrong??

Thanks again,

Elaine

Sub format()
'
Dim wb As Workbook
Dim wsheet As Worksheet 'not just an object

For Each wb In Application.Workbooks
wb.Activate

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

End Sub