CODEWITHSUNDEEP
arraysexcelvbaextract
LibertyJoe
LibertyJoe

Reputation: 21

Array VBA code (Data Extraction)

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

Upvotes: 1

Views: 691

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

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

Upvotes: 1

Related Questions