循环浏览选项卡并重新格式化列

Question

我有一个Excel文档，其中包含导入多个选项卡的数据。每个选项卡中的列都很长，很难阅读。

我正在尝试循环浏览选项卡并重新格式化列。

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

此代码生成

"运行时错误"9"：下标超出范围"；

调试器指向For循环内的第一行。

如果我将此代码作为测试运行，则不会出现错误：

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

我有两个问题：

1-如何循环而不出现错误
2-如何从第一个选项卡开始？

Answer 1

您很少需要使用带有Vba 的Select或Activate

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