autofit wont loop

nick612hayden

New Member
Joined
May 15, 2012
Messages
33
hey all

does anyone see why this code won't loop to the next open workbook...? it only does it on the current workbook ive selected, and therefore, cannot Call to another macro...

ideally, i would rather just have all sheets in all open workbooks autofit from columns "B:Z" but that hasn't worked either, any help is appreciated!


Sub Adjust_Column_Specific()
Dim wBook As Workbook
Dim wSheet As Worksheet


Application.ScreenUpdating = False

For Each wBook In Workbooks

Sheets("Self").Select
Columns("B:Z").AutoFit
Sheets("Employee Backup").Select
Columns("B:Z").AutoFit
Sheets("Employee Calcs").Select
Columns("B:Z").AutoFit


Next wBook

Application.ScreenUpdating = True


End Sub

Thanks!
Nick
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
try:
Code:
Sub Adjust_Column_Specific()
Dim wBook As Workbook
Application.ScreenUpdating = False
For Each wBook In Workbooks
  wBook.Sheets("Self").Columns("B:Z").AutoFit
  wBook.Sheets("Employee Backup").Columns("B:Z").AutoFit
  wBook.Sheets("Employee Calcs").Columns("B:Z").AutoFit
Next wBook
Application.ScreenUpdating = True
End Sub
 

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
  1. 2011
  2. 2010
Platform
  1. Windows
  2. MacOS
you do not reference wbooks.

You can either add the line:
wbooks.activate
after the "For each" or reference wbooks on each line.

You could rewrite this as:
Code:
Sub Adjust_Column_Specific()
Dim wBook As Workbook
Dim wSheet As Worksheet


Application.ScreenUpdating = False

For Each wBook In Workbooks

   wbook.Sheets("Self").Columns("B:Z").AutoFit
   wbook.Sheets("Employee Backup").Columns("B:Z").AutoFit
   wbook.Sheets("Employee Calcs").Columns("B:Z").AutoFit

Next wBook

Application.ScreenUpdating = True


End Sub
This code will obviously fail if any of the sheets are not in one of the open workbooks. I would add checks to see if wBook.name is one that you want or you can use "On Error Resume Next" to ignore the errors. (not the best but to each their own)
 

nick612hayden

New Member
Joined
May 15, 2012
Messages
33
Thanks guys, both answers are greatly appreciated! and sorted now with your help.

Was there a way to not call out specific sheets, and instead, just make it that every Sheet in every Workbook autofit columns B:Z? The solutions you provided work, but for future ref...

cheers
Nick
 
Last edited:

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
try:
Code:
Sub Adjust_Column_Specific()
Dim wBook As Workbook, sht As Worksheet
Application.ScreenUpdating = False
For Each wBook In Workbooks
  For Each sht In wBook.Sheets
    sht.Columns("B:Z").AutoFit
  Next sht
Next wBook
Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,494
Messages
5,625,083
Members
416,070
Latest member
Austen G

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top