Array VBA code (Data Extraction)

Question

Current;y working on extracting data (rows) based on criteria from a userform but the code below will only work on individual sheets Once the button is available on the sheet which I can get this code below to work and it writes to the "Slave" sheet as expected, but I cannot for the life of me get the correct complied code for arrays.

I've tried different code from various places including here but I'm not competent enough to debug the fault.

Could someone help me out here, or point me in the right direction?

Sub CommandButton1_Click()
    Dim strsearch As String, lastline As Integer, tocopy As Integer    
    strsearch = CStr(InputBox("enter the string to search for"))

        'Enter code for all sheets in here...

    lastline = Range("A65536").End(xlUp).Row
    j = 1

    For i = 1 To lastline
        For Each c In Range("A" & i & ":Z" & i)
            If InStr(c.Text, strsearch) Then
                tocopy = 1
            End If
        Next c
        If tocopy = 1 Then
            Rows(i).Copy Destination:=Sheets("Slave").Rows(j)
            j = j + 1
        End If
    tocopy = 0
    Next i
End Sub

I don't understand your question. What is the problem? what doesn't compile? what error? which lines gives the error? what have you tried to fix it? what's the start and end results? Please put more effort into asking a question, we are no mind-readers. — user2140173
– user2140173, Commented Dec 16, 2014 at 16:09

Tim Williams · Accepted Answer · 2014-12-16 17:17:09Z

1

There are faster ways to do this but this is just adding in the sheet looping

Sub CommandButton1_Click()
    Dim strsearch As String, lastline As Long 
    Dim sht as WorkSheet

    strsearch = CStr(InputBox("enter the string to search for"))
    j = 1

    For Each sht in ThisWorkbook.WorkSheets
    If sht.Name <> "Slave" Then

    lastline = sht.Cells(Rows.Count, 1).End(xlUp).Row

    For i = 1 To lastline
        For Each c In sht.Range("A" & i & ":Z" & i)
            If InStr(c.Text, strsearch) Then
                sht.Rows(i).Copy Destination:=Sheets("Slave").Rows(j)
                j = j + 1
                Exit For 'stop looking!
            End If
        Next c
    Next i

    End If
    Next sht

End Sub

answered Dec 16, 2014 at 17:17

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

Collectives™ on Stack Overflow

Array VBA code (Data Extraction)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related