Using Listbox to loop through specific sheets - VBA

Question

I am trying to loop through multiple worksheets and find values above a certain threshold. If those values are found, the whole line containing the value above threshold should be copied into a new created "Summary"-Sheet.

My UserForm so far looks like this:

And my code like this:

Option Explicit


Private Sub UserForm_Initialize()
Dim N As Long
For N = 1 To ActiveWorkbook.Sheets.Count
    Sheets_txt.AddItem ActiveWorkbook.Sheets(N).Name
Next N
End Sub

Private Sub CommandButton1_Click()

Dim SelectedItems As String

Dim column As String
Dim WS As Worksheet
Dim i As Long, j As Long, lastRow As Long, k As Long
Dim sh As Worksheet
Dim sheetsList As Variant
Dim threshold As Long

Set WS = ThisWorkbook.Worksheets.Add
WS.Name = "Summary"

threshold = Me.Threshold_txt.Value
column = Me.Column_txt.Value

j = 2

For k = 0 To Sheets_txt.ListCount - 1
    If Sheets_txt.Selected(i) = True Then
    SelectedItems = SelectedItems & Sheets_txt.List(i)
    lastRow = SelectedItems.Cells(SelectedItems.Rows.Count, "A").End(xlUp).Row
        For i = 4 To lastRow
            If SelectedItems.Range(column & i) > threshold Or SelectedItems.Range(column & i) < -threshold Then
                SelectedItems.Range("a" & i & ":n" & i).Copy Destination:=WS.Range("A" & j)
                WS.Range("N" & j) = SelectedItems.Name
                j = j + 1
            End If
        Next i
    End If
Next k
WS.Columns("A:N").AutoFit
End Sub


Private Sub CommandButton2_Click()
Unload Me
End Sub

However I am struggeling with the For loop. The code should be looping through all selected sheets and do the things I wrote above. However using a variable SelectedItems to store all strings that meet the condition of If Sheets_txt.Selected(i) = True is not working. In my case it debugs at lastRow = SelectedItems.Cells(SelectedItems.Rows.Count, "A").End(xlUp).Row and points to (SelectedItems.Rows.Count.

How can I get this loop working? Any help appreciated!

SelectedItems doesn't work quite as you might expect. I'll try to post back when I'm at my computer. I don't remember the exact syntax, but I believe you loop through Items and test whether they are selected. — Kyle
– Kyle, Commented Mar 26, 2017 at 16:16
@Kyle: Alright. To be honest I am totally stuck at this point. I have tried a lot, but cannot find the right syntax to loop through my selected sheets one after another.... — Florian Schramm
– Florian Schramm, Commented Mar 27, 2017 at 7:46

user3598756 · Accepted Answer · 2017-03-27 08:11:06Z

1

You could try this (untested) code.

UPDATE: The editor of this question made some slight changes to the inital code suggested and tested this code now.

Option Explicit

Private Sub UserForm_Initialize()
Dim N As Long
For N = 1 To ActiveWorkbook.Sheets.Count
    Sheets_txt.AddItem ActiveWorkbook.Sheets(N).Name
Next N
End Sub

Private Sub CommandButton1_Click()

    Dim SelectedItems As String
    Dim column As String
    Dim WS As Worksheet
    Dim i As Long, j As Long, lastRow As Long, k As Long
    Dim sh As Worksheet
    Dim sheetsList As Variant
    Dim threshold As Long

    Set WS = ThisWorkbook.Worksheets.Add
    WS.Name = "Summary"

    threshold = Me.Threshold_txt.Value
    column = Me.Column_txt.Value

    j = 1
    For k = 0 To Sheets_txt.ListCount - 1
        If Sheets_txt.Selected(k) = True Then
            With Worksheets(Sheets_txt.List(k))
                lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                For i = 4 To lastRow
                    If .Cells(i, column) > threshold Or  .Cells(i, column) < -threshold Then
                         j = j + 1
                         Intersect(.Range("A:N"), .Cells(i, column).EntireRow).Copy Destination:=WS.Cells(j,2)
                         WS.Cells(j, "A")= .Name
                     End If
                Next
            End With
            If WS.Cells(j, "A")= .Name then j = j + 1 '<--| add a blank line if current sheet has produced at least one pasted line
        End If
    Next
    WS.Columns("A:N").AutoFit
End Sub


Private Sub CommandButton2_Click()
    Me.Hide 'and move the 'Unload' command in the sub calling the userform
End Sub

edited Mar 27, 2017 at 8:11

answered Mar 26, 2017 at 16:19

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Florian Schramm Over a year ago

It debugs at Intersect(.Range("A:N") .Cells(i, column).EntireRow.Copy Destination:=WS.Cells(j,1) saying there is an sytax error

user3598756 Over a year ago

There was a missing parenthesys and comma. See edited code

Florian Schramm Over a year ago

It is not really working. When I select the first Sheet in my listbox it loops through is, however stops after the first loop telling me that argument selected is invalid and pointing to If Sheets_txt.Selected(i) = True Then. Furhermore the loop works only for Sheet1. If i select i.e Sheet2 and Sheet3 in my listbox I get an empty "Summary" Sheet. Any idea what could be missing? Would appreciate your help!

Florian Schramm Over a year ago

Ok, I figured it out! I had to replace the "i" for "k" as I am looping first through "k" and afterrwards through "i". Works perfectly! Thanks a lot!

Florian Schramm Over a year ago

Just one last question: If I use this code all my values above threshold are copied into my summary sheet right after another. Can I somehow insert a blank row after each loop, so that it is more straight forward to see each sheet with the corresponding values (despite the naming of each sheet in column "A")?!

|

Collectives™ on Stack Overflow

Using Listbox to loop through specific sheets - VBA

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related