vba 循环直到最后一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12373560/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Loop until last worksheet
提问by user1624926
I am trying to apply a format to every worksheet in my current workbook. I have tried "For Each" and I have tried to Loop through until I reach last worksheet but both error due to different reasons. Can someone please tell me what I'm doing wrong?
我正在尝试将格式应用于当前工作簿中的每个工作表。我尝试过“For Each”，并且尝试循环遍历直到到达最后一个工作表，但由于不同的原因而导致两个错误。有人可以告诉我我做错了什么吗？
Method 1: It works on the 1st worksheet but not on the remaining worksheets.
方法 1：它适用于第一个工作表，但不适用于其余工作表。
Sub format_worksheets()
Dim ws As Worksheet
For Each ws In Worksheets
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False,
Other:=True,TrailingMinusNumbers:=True
Range("A1").Select
Next ws
End Sub
Method 2: It doesn't recognise the last worksheet.
方法 2：它不识别最后一个工作表。
Sub format_worksheets()
Dim ws As Worksheet
ws = Worksheet.Active
Do
code
Loop Until ws = Sheets(Sheets.Count).Active
End Sub
回答by LittleBobbyTables - Au Revtheitroad
In method one, try changing:
在方法一中，尝试更改：
Columns("A:A").Select
To
到
ws.Activate
ws.Columns("A:A").Select
... to activate the worksheet and specify the worksheet you want to work with.
... 激活工作表并指定要使用的工作表。
回答by Daniel
I was trying to figure out what you were trying to do with method 2, and I could not. But just because, here's a working version that does not use for each.
我试图弄清楚你想用方法 2 做什么，但我不能。但仅仅因为，这里有一个并不适用于每个的工作版本。
Sub format_worksheets()
dim x as Integer
For x = 1 To Sheets.Count
Sheets(x).Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=True, TrailingMinusNumbers:=True
Next x
End Sub
Note that this will error if any sheet has no data in Column A. I also left out the select statements because it's best practice to avoid them if they are not necessary.
请注意，如果任何工作表在 A 列中没有数据，这将出错。我也省略了 select 语句，因为如果不需要它们，最好避免它们。
