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

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." wrote:

> 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, 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" wrote:

> 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." wrote:
>
> > 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

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



"Elaine J." wrote:

> 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" wrote:
>
> > 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." wrote:
> >
> > > 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