autofit in multiple workbooks

[Elaine J.]

I have 16 workbooks open. I want to go through each one and autofit the
columns. Specifically I want to autofit the columns, then save and close
each workbook. The workbooks are already named and saved once, but I need to
resave the changes.

This is the code that I am using:

Dim wsheet As Range
Dim wb As Workbook
Dim wWorkbooks As Collection

For Each wb In Workbooks

Set wsheet = ActiveWorkbook.Worksheets("Sheet1").Range("A1:AG25")

With wsheet
.Select
.Columns.AutoFit
.save
.close
End With

next wb


It will autofit the columns on about three of the files and save (but not
close). Then it just stops.

Any help would be appreciated.

Elaine

 

[Joel]

The for statement doesn't activate the workbook, just give a list of workbooks
You need the following change (add wb.name)

For Each wb In Workbooks

Set wsheet = workbooks(wb.name).Worksheets("Sheet1").Range("A1:AG25")

 

[Elaine J.]

Joel, thanks for your reply, but I am afraid I am confused. For instance
say I have three workbooks (I actually will have 17) open (ie. Arts.xls,
Crafts.xls and Sewing.xls). Are you saying that I have to name each one of
the workbooks that are opened? I guess I was under the impression that
ActiveWorkbook took the place of naming the actual workbook. I thought I
could work with the workbook that was active, save it and the next workbook
would be active. Obviously I am missing something here.

Can you give me a specific example using the workbooks named above?

Thanks so much.

 

[Joel]

You just had to change the one line that I had shown below. Doing the FOR wb
just references each workbook, it doesn't make the workbook the active
workbook. You just need to reference the new wb. Look at the changes below,
you'll understand.

Dim wsheet As Range
Dim wb As Workbook
Dim wWorkbooks As Collection

For Each wb In Workbooks

'old code
'Set wsheet = ActiveWorkbook.Worksheets("Sheet1").Range("A1:AG25")
'new code
Set wsheet = workbooks(wb.name).Worksheets("Sheet1").Range("A1:AG25")

With wsheet
.Select
.Columns.AutoFit
.save
.close
End With

next wb