cycle through the tabs and reformat the columns-CodePudding

I have an excel document consisting of data imported into multiple tabs, and the columns in each tab are super stretched out and hard to read.

I am trying to make a small macro that would cycle through the tabs and reformat the columns.

Here is the code I have tried so far:

Sub resize_columns()
'cycles through each tab (have to start in first tab)
'selects all columns and autofits them
 
Dim i As Integer

Dim num As Integer

num = ThisWorkbook.Sheets.Count - 1 'counts number of sheets to use in loop range

For i = 0 To num

    Sheets(ActiveSheet.Index   1).Activate
    Columns("A:Q").Select
    Columns("A:Q").EntireColumn.AutoFit
    
Next i
    
End Sub

This code produces a

"Run-time error '9': Subscript out of range"

and the debugger points to the first line inside the For loop.

However, if I run this code here as a test, there is no error:

Sub resize_columns()
'cycles through each tab (have to start in first tab)
'selects all columns and autofits them

Dim i As Integer

For i = 1 To 2

    Sheets(ActiveSheet.Index   1).Activate
    ActiveSheet.Columns("A:Q").Select
    ActiveSheet.Columns("A:Q").EntireColumn.AutoFit
    
    
Next i
    
End Sub

Have two questions, basically:

1 - how do I make it loop through without the error? 2 - how do I make it start on the first tab?

I just started working with VBA two days ago, so very raw beginner here. If someone has some advice, would really appreciate it!

Thanks!

CodePudding user response：

You will rarely need to use Select or Activate with Vba

Sub resize_columns()
   Dim ws As Worksheet
   For Each ws In ThisWorkbook.Sheets
      ws.Columns("A:Q").AutoFit
   Next
End Sub