使用VBA在除excel中的已命名工作表之外的所有工作表上运行特定宏 [英] Running a specific macro on all sheets besides named ones in excel using VBA
本文介绍了使用VBA在除excel中的已命名工作表之外的所有工作表上运行特定宏的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个代码,该代码调用其他宏以在指定的工作表上运行.
I have a code which calls other macros to run on specified sheets.
首先:我想知道如何告诉它在以前未列出的所有剩余工作表上运行另一个宏
Firstly: I was wondering how to tell it to run another macro on all remaining sheets that haven't been previously listed
第二:我希望上面的宏在除许多已命名工作表(例如图形")之外的其余工作表上运行
Secondly: I want the above macro to run on the remaining sheets excluding a number of named sheets (e.g. "graphs")
到目前为止,这是我的代码
Here is my code so far
Sub specify_sheets()
'apply appropriate limits to relative sheet
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name = Worksheets("NB12") Or _
sht.Name = Worksheets("NB15") Then
Call limits_Alluvium
End If
If sht.Name = Worksheets("NB24") Then
Call limits_BOCOBOML_GFA
End If
If sht.Name = Worksheets("NB16") Or _
sht.Name = Worksheets("NB17") Or _
sht.Name = Worksheets("NB19") Or _
sht.Name = Worksheets("NB20") Or _
sht.Name = Worksheets("Bore 31") Then
Call limits_BOCOBOML_MIA
End If
If sht.Name = Worksheets("Bore 47") Or _
sht.Name = Worksheets("Bore 48") Then
Call limits_FracturedRock_GFA
End If
If sht.Name = Worksheets("Bore 4") Or _
sht.Name = Worksheets("Bore 4a") Or _
sht.Name = Worksheets("Bore 40") Then
Call limits_FracturedRock_MIA_West
End If
If sht.Name = Worksheets("Bore 30") Then
Call limits_FracturedRock_MIA_East
End If
Next sht
End Sub
这是它调用的其中一个宏的代码:
Here is the code from one of the macros it calls on:
Sub limits_Monitoring_bores()
Dim sht As Worksheet, lastRow As Long
Set sht = ActiveWorkbook.Worksheet
'Name columns appropriately
With ActiveWorkbook.Worksheets(1)
.Cells(1, 4).Value = "Min"
.Cells(1, 5).Value = "Max"
.Cells(1, 7).Value = "20th Percentile"
.Cells(1, 8).Value = "80th Percentile"
.Cells(1, 10).Value = "20th Percentile"
.Cells(1, 11).Value = "80th Percentile"
End With
lastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
sh.Range("D2:D" & lastRow).Value = "=6"
sh.Range("E2:E" & lastRow).Value = "=8.5"
sh.Range("G2:G" & lastRow).Value = "=PERCENTILE(F:F,0.2)"
sh.Range("H2:H" & lastRow).Value = "=PERCENTILE(F:F,0.8)"
sh.Range("J2:J" & lastRow).Value = "=PERCENTILE(I:I,0.2)"
sh.Range("K2:K" & lastRow).Value = "=PERCENTILE(I:I,0.8)"
End Sub
推荐答案
这应该对您有用:-
Sub STO()
Dim Fun As String
Dim Sht As Worksheet
For Each Sht In Worksheets
Select Case Sht.Name
Case "NB12", "NB15"
limits_Alluvium
Case "NB24"
limits_BOCOBOML_GFA
Case "NB16", "NB17", "NB19", "NB20", "Bore 31"
limits_BOCOBOML_MIA
Case "Bore 47", "Bore 48"
limits_FracturedRock_GFA
Case "Bore 4", "Bore 4a", "Bore 40"
limits_FracturedRock_MIA_West
Case "Bore 30"
limits_FracturedRock_MIA_East
Case Else
If Len(Fun) Then Fun = Fun & vbCr
Fun = Fun & Sht.Name
End Select
Next Sht
MsgBox Fun, vbInformation, "Sheets not processed"
End Sub
未在任何地方列出的表格将不会得到处理.
Sheets not listed anywhere will not be processed.
这篇关于使用VBA在除excel中的已命名工作表之外的所有工作表上运行特定宏的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文