filter custom rows based on criteria of columns like column1, column2, column3 & copy complete row in another sheet (sheet2)

[meer_ali]

Hello friends,

I have a code which is working good for column1 criteria, but i need to drill down further for column2, column3 filtering.. and then copying those filetered records into another sheet(sheet2).

my query is to filter custom rows based on criteria of columns like column1, column2, column3 & copy complete row in another sheet (sheet2)

Pls check the code which is working good for column1 filtering, but still need to filter for column1 & column2.

Pls guide here...

Thanks.
Code:

Sub CustomCopy()
Dim strsearch As String
Dim lastline As Long, toCopy As Long
Dim searchColumn As String
Dim i As Long, j As Long
Dim c As Range
strsearch = CStr(InputBox("Enter the value to search for"))
lastline = Range("B" & Rows.Count).End(xlUp).Row
j = 1
For i = 1 To lastline
If Range("B" & i).Value = strsearch Then
   Rows(i).Copy Destination:=Sheets(2).Rows(j)
   j = j + 1
End If
Next
MsgBox j - 1 & " row(s) copied to Sheet2."
End Sub

 

[JLGWhiz]

See how this works.

Sub CustomCopy()
Dim strsearch As String
Dim lastline As Long, toCopy As Long, lastcol As Long
Dim searchColumn As String
Dim i As Long, j As Long, k As Long
Dim c As Range
strsearch = CStr(InputBox("Enter the value to search for"))
lastline = Range("B" & Rows.Count).End(xlUp).Row
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
j = 1
    For k = 2 To lastcol
        For i = 1 To lastline
            If Cells(k, i).Value = strsearch Then
                Rows(i).Copy Destination:=Sheets(2).Rows(j)
                j = j + 1
            End If
        Next
    Next
MsgBox j - 1 & " row(s) copied to Sheet2."
End Sub

 

[meer_ali]

Dear JLGWhiz,

Appreciate your efforts. sorry to tell that the code is not working for single column. maybe something wrong in loop.

Pls check it once again..

thanks.

See how this works.

Sub CustomCopy()
Dim strsearch As String
Dim lastline As Long, toCopy As Long, lastcol As Long
Dim searchColumn As String
Dim i As Long, j As Long, k As Long
Dim c As Range
strsearch = CStr(InputBox("Enter the value to search for"))
lastline = Range("B" & Rows.Count).End(xlUp).Row
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
j = 1
    For k = 2 To lastcol
        For i = 1 To lastline
            If Cells(k, i).Value = strsearch Then
                Rows(i).Copy Destination:=Sheets(2).Rows(j)
                j = j + 1
            End If
        Next
    Next
MsgBox j - 1 & " row(s) copied to Sheet2."
End Sub

 

[JLGWhiz]

Guess I don't understand what you want. The code I suggested works in a test setup to copy rows where the search value is found. The only change to your original code is to add a loop that starts in column B and goes through the last column. So theoretically, if your original code worked for one column, the suggested code should work for all but column A. If you want to include column A, then change 'For k = 2 To lastcol' to 'For k = 1 To lastcol'.

 

[meer_ali]

suppose b column has names upto 20 rows; c column as true & false values randomly; and d column has status - working, pending, done, etc.. and E column has description... now i want to set the b column and c column & d column as criteria for filtering those rows only whatever criteria i will chose everytime differently as per my need everytime... and copying into another sheet (say sheet2) as usual when i click this macro. my code is doing that work for only 1 column criteria filter& copy, i need 2 more criteria to be asked for doing that same job.... Hope, i conveyed the issue properly now.

Thanks.

 

[JLGWhiz]

suppose b column has names upto 20 rows; c column as true & false values randomly; and d column has status - working, pending, done, etc.. and E column has description... now i want to set the b column and c column & d column as criteria for filtering those rows only whatever criteria i will chose everytime differently as per my need everytime... and copying into another sheet (say sheet2) as usual when i click this macro. my code is doing that work for only 1 column criteria filter& copy, i need 2 more criteria to be asked for doing that same job.... Hope, i conveyed the issue properly now.

Thanks.

You would need to use AdvancedFilter and set up contiguous cells in a range to use as your criteria range.
Example. On a new sheet3 in cells A1 enter the column header for sheet1 Column B and in the cell under that enter the search value for column B, in sheet3 B1 enter header for column C and under that enter search value for column C and in sheet3 C1 enter the header for column D and under that enter the search value for column D. Then run the code below.

. 
Sub t()
Sheet1.UsedRange.AdvancedFilter xlFilterInPlace, Sheet3.Range("A1:C3")
Sheet1.UsedRange.Offset(1).Copy Sheet2.Cells(Rows.Count, 1).End(xlUp)(2)
Sheet1.lShowAllData
End Sub

you can change the values on row 2 of sheet3 to filter new criteria.