VBA-在工作簿中选择特定的工作表以循环浏览 [英] VBA - select specific sheets within workbook to loop through

问题描述

我有一张工作表可变的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-在工作簿中选择特定的工作表以循环浏览的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！