每个工作表循环的 Excel VBA [英] Excel VBA For Each Worksheet Loop

问题描述

我正在编写代码，基本上遍历工作簿中的每个工作表，然后更新列宽.下面是我写的代码；我没有收到任何错误，但它实际上也没有做任何事情.非常感谢任何帮助！

I am working on code to basically go through each sheet in my Workbook, and then update column widths. Below is the code I wrote; I don't receive any errors, but it also doesn't actually do anything. Any help is greatly appreciated!

 Option Explicit
 Dim ws As Worksheet, a As Range

Sub forEachWs()

For Each ws In ActiveWorkbook.Worksheets
Call resizingColumns
Next

End Sub

Sub resizingColumns()
Range("A:A").ColumnWidth = 20.14
Range("B:B").ColumnWidth = 9.71
Range("C:C").ColumnWidth = 35.86
Range("D:D").ColumnWidth = 30.57
Range("E:E").ColumnWidth = 23.57
Range("F:F").ColumnWidth = 21.43
Range("G:G").ColumnWidth = 18.43
Range("H:H").ColumnWidth = 23.86
Range("i:I").ColumnWidth = 27.43
Range("J:J").ColumnWidth = 36.71
Range("K:K").ColumnWidth = 30.29
Range("L:L").ColumnWidth = 31.14
Range("M:M").ColumnWidth = 31
Range("N:N").ColumnWidth = 41.14
Range("O:O").ColumnWidth = 33.86
End Sub

推荐答案

尝试稍微修改您的代码:

Try to slightly modify your code:

Sub forEachWs()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Call resizingColumns(ws)
    Next
End Sub

Sub resizingColumns(ws As Worksheet)
    With ws
        .Range("A:A").ColumnWidth = 20.14
        .Range("B:B").ColumnWidth = 9.71
        .Range("C:C").ColumnWidth = 35.86
        .Range("D:D").ColumnWidth = 30.57
        .Range("E:E").ColumnWidth = 23.57
        .Range("F:F").ColumnWidth = 21.43
        .Range("G:G").ColumnWidth = 18.43
        .Range("H:H").ColumnWidth = 23.86
        .Range("i:I").ColumnWidth = 27.43
        .Range("J:J").ColumnWidth = 36.71
        .Range("K:K").ColumnWidth = 30.29
        .Range("L:L").ColumnWidth = 31.14
        .Range("M:M").ColumnWidth = 31
        .Range("N:N").ColumnWidth = 41.14
        .Range("O:O").ColumnWidth = 33.86
    End With
End Sub

注意，resizingColumns 例程采用参数 - 范围所属的工作表.

Note, resizingColumns routine takes parametr - worksheet to which Ranges belongs.

基本上，当您使用 Range("O:O") - 范围从 ActiveSheet 开始的代码操作时，这就是您应该使用 With ws 的原因 语句，然后 .Range("O:O").

Basically, when you're using Range("O:O") - code operats with range from ActiveSheet, that's why you should use With ws statement and then .Range("O:O").

而且没有必要使用全局变量(除非你在其他地方使用它们)

And there is no need to use global variables (unless you are using them somewhere else)

这篇关于每个工作表循环的 Excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！