autofit wont loop

[nick612hayden]

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

 

[p45cal]

try:

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]

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:

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]

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

 

[p45cal]

try:

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