问题描述
我有一张工作表可变的excel工作簿.目前,我在所有工作表和特定列中进行循环搜索,以搜索超过特定阈值的图形.列和阈值由需要由用户填写的输入框确定.如果该列中的数字为"J"列,并且第10行高于阈值,则将第10行复制并粘贴到新创建的摘要"工作表等中.
I have an excel workbook with a variable number of sheets. At the moment I am looping through all sheets and therein a specific column to search for figures above a certain threshold. Column and threshold are determined by inputboxes that need to be filled in by the user. If the figure in the column, let's say column "J" and row 10 is above threshold, row 10 is copied and pasted in a new created "summary" sheet etc.
目前,我正在为选择特定的纸张而苦苦挣扎.我并不总是想要遍历所有工作表,而是想拥有另一个输入框或其他我可以在其中选择要遍历的特定工作表(STRG +"sheetx","sheety"等...)的东西!有人知道我如何用我的代码来完成吗?我知道我必须更改我的"for each"语句来代替所选的工作表,但是我不知道如何创建输入框来选择特定的选项卡...
I am struggling at the moment with a specific selection of sheets. I don't always want to loop through all sheets but instead would like to have another inputbox or something else in which I can select specific sheets (STRG + "sheetx" "sheety" etc...) that are looped through?! Anyone an idea how I can accomplish that with my code? I know that I have to change my "for each" statement to substitute for the selected sheets but I don't know how to create the inputbox to select specific tabs...
任何帮助表示赞赏!
Option Explicit
Sub Test()
Dim column As String
Dim WS As Worksheet
Dim i As Long, j As Long, lastRow As Long
Dim sh As Worksheet
Dim sheetsList As Variant
Dim threshold As Long
Set WS = GetSheet("Summary", True)
threshold = Application.InputBox("Input threshold", Type:=1)
column = Application.InputBox("Currency Column", Type:=2)
j = 2
For Each sh In ActiveWorkbook.Sheets
If sh.Name <> "Summary" Then
lastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
For i = 4 To lastRow
If sh.Range(column & i) > threshold Or sh.Range(column & i) < -threshold Then
sh.Range("a" & i & ":n" & i).Copy Destination:=WS.Range("A" & j)
WS.Range("N" & j) = sh.Name
j = j + 1
End If
Next i
End If
Next sh
WS.Columns("A:N").AutoFit
End Sub
Function GetSheet(shtName As String, Optional clearIt As Boolean = False) As Worksheet
On Error Resume Next
Set GetSheet = Worksheets(shtName)
If GetSheet Is Nothing Then
Set GetSheet = Sheets.Add(after:=Worksheets(Worksheets.Count))
GetSheet.Name = shtName
End If
If clearIt Then GetSheet.UsedRange.Clear
End Function
推荐答案
在"NO-UserForm" 心情中,设置时可以结合使用Dictionary
对象和Application.InputBox()
方法将其Type
参数设置为8
,并使其接受range
选择:
in the "NO-UserForm" mood you could use a combination of Dictionary
object and the Application.InputBox()
method when setting its Type
parameter to 8
and have it accept range
selections:
Function GetSheets() As Variant
Dim rng As Range
On Error Resume Next
With CreateObject("Scripting.Dictionary")
Do
Set rng = Nothing
Set rng = Application.InputBox(prompt:="Select any range in wanted Sheet", title:="Sheets selection", Type:=8)
.item(rng.Parent.Name) = rng.Address
Loop While Not rng Is Nothing
GetSheets = .keys
End With
End Function
此功能使Parent
工作表名称超出用户切换工作表所选择的每个范围,并在用户单击Cancel
按钮或关闭InputBox时停止
this function gets the Parent
sheet name out of each range selected by the user switching through sheets and stops when the user clicks the Cancel
button or closes the InputBox
被主"子程序利用,如下所示:
to be exploited by your "main" sub as follows:
Sub main()
Dim ws As Worksheet
For Each ws In Sheets(GetSheets) '<--| here you call GetSheets() Function and have user select sheets to loop through
MsgBox ws.Name
Next
End Sub
这篇关于VBA-在工作簿中选择特定的工作表以循环浏览的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!