Problem with Autofit loop for each worksheet

Hi everyone,

First day here and I've run into two problems. I was fortunate enough to have my first one quickly answered by Charles. I'm pretty sure this forum will go 2 for 2 today since this problem seems to be a lot simpler (at least amount of code wise).

This macro is going to be used by multiple workbooks (but only one at a time). Each workbook will have a different number of worksheets, but will always need Columns A through H to be autofitted. Here is my code:

Sub FitNow()
'Autofit main columns of workbook
Dim i, j As Integer
Set j = Sheet.Count

For i = 1 To j
ActiveWorkbook.Sheets(i).Columns("A:H").EntireColumn.AutoFit
Next

End Sub

Any idea why this isn't working? I'm pretty bad at loops, so that could be the main reason.

Thank you in advance for your help and insight

hi,

Rylo's given an answer, but the syntax reminded my of a tip on Chip's site:

When looping through a collection it is usually faster than the FOR EACH statement rather than using the index. For example, the first code loop is faster than the second:

Dim WS as Worksheet
For Each WS In Worksheets
    MsgBox WS.Name
Next WS

Dim i as Integer
For i = 1 To Worksheets.Count
    MsgBox Worksheets(i).Name
Next i

sourced from: http://www.cpearson.com/Excel/optimize.htm

btw, the below line of code

Dim i, j As Integer
'is the equivalent of
Dim i as Variant, j As Integer
'I would personally change it to 
Dim i as Long, j As Long
'because I've read that many computers (I think it relates to the version of VBA?) now internally convert "integers" to "longs" before using them in code.

hth
Rob

Beauty, I'm pleased we could help. Thanks for the feedback & marking the post as Solved

Rob