vba 需要宏来循环所有工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7274957/
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
Need macro to cycle through all worksheets
提问by QuestionAsker
I have the following macro and I need it to perform the same operation on all the worksheets in the workbook, not just the active one. I've tried tons of different stuff, but I don't have much experience with excel so I'm not having much luck.
我有以下宏，我需要它对工作簿中的所有工作表执行相同的操作，而不仅仅是活动的工作表。我尝试了很多不同的东西，但我对 excel 没有太多经验，所以我运气不佳。
Here's the macro.
这是宏。
Sub Tester()
Dim c As Range, rngMerge As Range
For Each c In ActiveSheet.Range("A1").Resize(1, 100).Cells
Set rngMerge = c.MergeArea
If rngMerge.Cells.Count > 1 Then
c.UnMerge
rngMerge.Value = rngMerge.Cells(1).Value
End If
c.Value = JoinUp(c.Resize(3, 1), "_")
Next c
ActiveSheet.Range("A2:A3").EntireRow.Delete
End Sub
Function JoinUp(rng As Range, Optional Delim As String = "") As String
Dim c As Range, rv As String
For Each c In rng.Cells
If Len(c.Value) > 0 Then
rv = rv & IIf(Len(rv) > 0, Delim, "") & c.Value
End If
Next c
JoinUp = rv
End Function
回答by Tim Williams
Sub ProcessAll()
Dim sht as worksheet
for each sht in ActiveWorkbook.Worksheets
ProcessSheet sht
next sht
End sub
Sub ProcessSheet(sht as worksheet)
Dim c As Range, rngMerge As Range
For Each c In sht.Range("A1").Resize(1, 100).Cells
Set rngMerge = c.MergeArea
If rngMerge.Cells.Count > 1 Then
c.UnMerge
rngMerge.Value = rngMerge.Cells(1).Value
End If
c.Value = JoinUp(c.Resize(3, 1), "_")
Next c
sht.Range("A2:A3").EntireRow.Delete
End Sub
Function JoinUp(rng As Range, Optional Delim As String = "") As String
Dim c As Range, rv As String
For Each c In rng.Cells
If Len(c.Value) > 0 Then
rv = rv & IIf(Len(rv) > 0, Delim, "") & c.Value
End If
Next c
JoinUp = rv
End Function
回答by Bruno Leite
or use
或使用
dim sht as worksheet
for each sht in thisworkbook.sheets
debug.print sht.name
next sht
回答by dcp
To loop over all the sheets in a workbook, you can use:
要遍历工作簿中的所有工作表，您可以使用：
dim i as Integer
For i = 1 To ActiveWorkBook.WorkSheets.Count
' do stuff
ActiveWorkbook.Worksheets(i) ....
Next i
回答by minaz
try:
尝试：
Sheets(Array("Sheet2", "Sheet1")).Select
Sheets("Sheet2").Activate
or if you want to cycle through:
或者如果你想循环：
For Each aSheet In ActiveWorkbook.Sheets
Next aSheet
