search in multiple sheets

[abdelfattah]

hi, all
i have data in multiple sheets begins from a2:q all of sheets the same thing the search in sheet1 in range a2 so i would when i search it get data from multiple sheets based on column d in multiple sheets if i clear the range a2 in sheet1 get all data
this output after search

استعلام.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ID	EMPLOYEE
2	1000
3
4
5
6
7	ITEM	1	DATE	CASE	DEP	2	3	4	5	6	CUSTOM	7	CASE1	MONTH	NUMBER	NAME	DOC
8	1			YES	DEP1						0		COMMON	FIRST	25	A1	5030
9	2			NO	DEP2						50		COMMON	FIRST	10	A1	5000
10	3			YES	DEP3						0		COMMON	FIRST	5	A1	6000
11	4			NO	DEP4						0		COMMON	FIRST	100	BA	7000
12	5			YES	DEP5						0		COMMON	FIRST	15	BA	8000
13	6			NO	DEP6						0		COMMON	FIRST	9	AA	1000
14	7			YES	DEP7						0		COMMON	FIRST	12	AA	1100
15	8			NO	DEP8						0		COMMON	FIRST	25	AA	1200
SHEET1

 

[DanteAmor]

i have data in multiple sheets begins from a2:q

You can put a sample of one of those sheets.

 

[abdelfattah]

this is two sheets

استعلام.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	ITEM	1		ID	CASE	DEP	2	3	4	5	6	CUSTOM	7	CASE1	MONTH	NUMBER	NAME	DOC
2	1			1000	YES	DEP1						0		COMMON	FIRST	25	A1	5030
3	3			1000	YES	DEP3						0		COMMON	FIRST	5	A1	6000
4	4			1000	NO	DEP4						0		COMMON	FIRST	100	BA	7000
5	5			1000	YES	DEP5						0		COMMON	FIRST	15	BA	8000
6	6			1000	NO	DEP6						0		COMMON	FIRST	9	AA	1000
7	7			11000	YES	DEP7						0		COMMON	FIRST	12	AA	1100
8	8			11000	NO	DEP8						0		COMMON	FIRST	25	AA	1200
1

استعلام.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	ITEM	1		ID	CASE	DEP	2	3	4	5	6	CUSTOM	7	CASE1	MONTH	NUMBER	NAME	DOC
2	1			1000	YES	DEP1						0		COMMON	FIRST	10	A1	5030
3	3			1500	YES	DEP3						0		COMMON	FIRST	22	A1	6000
4	4			1400	NO	DEP4						0		COMMON	FIRST	33	BA	7000
5	5			1000	YES	DEP5						0		COMMON	FIRST	44	BA	8000
6	6			1000	NO	DEP6						0		COMMON	FIRST	9	AA	1000
7	7			11000	YES	DEP7						0		COMMON	FIRST	12	AA	1100
8	8			11000	NO	DEP8						0		COMMON	FIRST	25	AA	1200
2

 

[DanteAmor]

Put the code in SHEET1 events

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sh As Worksheet, sh1 As Worksheet
  Dim a As Variant, b() As Variant, nID As Variant, cols As Variant
  Dim lr As Long, lr2 As Long, i As Long, j As Long, k As Long
 
  If Target.Address(0, 0) = "A2" Then
    If Target.CountLarge > 1 Then Exit Sub
    Set sh1 = Sheets("SHEET1")
    nID = Target.Value
    sh1.Rows("8:" & Rows.Count).ClearContents
    cols = Array(1, 2, 3, 0, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
    For Each sh In Sheets
      lr = lr + sh.Range("D" & Rows.Count).End(3).Row
    Next
    ReDim b(1 To lr, 1 To 17)
    '
    For Each sh In Sheets
      If sh.Name <> sh1.Name Then
        a = sh.Range("A2:R" & sh.Range("D" & Rows.Count).End(3).Row).Value2
        For i = 1 To UBound(a, 1)
          If a(i, 4) = IIf(nID = "", a(i, 4), nID) Then
            k = k + 1
            For j = 1 To UBound(a, 2)
              If cols(j - 1) <> 0 Then b(k, cols(j - 1)) = a(i, j)
            Next j
          End If
        Next i
        Erase a
      End If
    Next sh
    '
    If k > 0 Then
      sh1.Range("A8").Resize(k, 17).Value = b
    Else
      MsgBox "No data"
    End If
  End If
End Sub

 

[abdelfattah]

great code thanks dante but i have aquestion if you don't mind jut curiosity if i decide specific column transfer how is it

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.