Copy result from different sheet according to condition.

[dheeraj1688]

Hi,
Actually i'm trying data from sheet2 & sheet3 ... sheetN to sheet1, according to condition provide in macro.
As per attached sheet.
i'm searching "FL" in different sheets and if found then adjacent cell/result cell copied to sheet1. Same thing if i found "FL" sheet2, it also mention sheet name in sheet1 result. So that i come to know from where this result come from.

 

[Marc L]

Hi ! Try this :​

Sub Demo()
            Application.ScreenUpdating = False
With Sheet1.UsedRange.Rows
      C& = .Columns(1).Column
      R& = .Item(2).Row
        If .Count > 1 Then .Item("2:" & .Count).Clear
End With
For W& = 2 To Worksheets.Count
    With Worksheets(W).UsedRange
           .Cells(1)(0).Value = 1
           .Cells(1)(0).Resize(.Rows.Count + 1).AutoFilter 1, "gl"
           N& = .Parent.Evaluate("SUBTOTAL(103," & .Columns(1).Address & ")")
        If N Then
            .Copy Sheet1.Cells(R, C)
            Sheet1.Cells(R, C + 2).Resize(N).Value = .Parent.Name
            R = R + N
        End If
          .AutoFilter
          .Cells(1)(0).Clear
    End With
Next
            Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !

Edit for optimization …
​

 

[dheeraj1688]

Thanks for very quick reply .. I appreciate your answer.
Thank u. And
One more question if i want "gl" as well as "fl" or may be more than 2 (attach example/Above example).

fl | bye | sheet2
fl | bye | sheet3
gl | hi | sheet2
gl | hi | sheet3

 

[Marc L]

I amended previous code for correction …

For two criterias only :​

Sub Demo2()
            Application.ScreenUpdating = False
With Sheet1.UsedRange.Rows
      C& = .Columns(1).Column
      R& = .Item(2).Row
        If .Count > 1 Then .Item("2:" & .Count).Clear
End With
For W& = 2 To Worksheets.Count
    With Worksheets(W).UsedRange
          .Cells(1)(0).Value = 1
          .Cells(1)(0).Resize(.Rows.Count + 1).AutoFilter 1, "fl", xlOr, "gl"
          N& = .Parent.Evaluate("SUBTOTAL(103," & .Columns(1).Address & ")")
        If N Then
            .Copy Sheet1.Cells(R, C)
            Sheet1.Cells(R, C + 2).Resize(N).Value = .Parent.Name
            R = R + N
        End If
          .AutoFilter
          .Cells(1)(0).Clear
    End With
Next
            Application.ScreenUpdating = True
End Sub

You may Like it !

This is the old fashion way until 2003 Excel version (often my test version).
Since 2007 version, you can use more than two criterias via an array,
see AutoFilter method in VBA inner help as samples in this forum …
​

 

[dheeraj1688]

Hi..
Thanks for reply!!
I 'm using the code, facing some problems :-
1. i'm using more than 2 word for filter.
2. Result is not coming in desired location.
3. I highlight Cell where i want to enter result from different sheets.

 

[Marc L]

This is the old fashion way until 2003 Excel version (often my test version).
Since 2007 version, you can use more than two criterias via an array,
see AutoFilter method in VBA inner help as samples in this forum …

1. Share the code you have done for more than 2 criteria words …
1. And how can we guess which criterias you use ?‼ Always the same ?
1. Or one day is one criteria, the next 3 and another only 2 ?!

2. As you can see in my demonstrations as Copy method VBA inner help,
2. just define the right Destination …

If you have code nothing, 'cause my usual test version can't filter
more than two criterias (or one by one using a loop),
since Excel 2007 version, just activate Macro recorder and do your own
more than 2 criterias filter manually : you will get the code !

Other way you can study : using an advanced filter …

Your last attachment is far away from initial one …
It's weird in initial post to not attach a workbook reflecting the real one !
​