Search from All sheet

[atuljadhavnetafim]

Dear Expert,

i am looking for VBA code which search value from all sheet from particular column and paste all data in last sheet.
e.g. i have one file which have 5 sheets and name of the sheets as below
1) Search
2) Bank1
3) Bank2
4) Bank3
5) Bank4

in search sheet, B3 cell contain search value for e.g it is 100000
and rest all sheet having same format and the amount store in E column in all sheet

now when i search 100000 (Exact match) then VBA give search this value from all Bank sheet and past entire row in search sheet after row 8 with sheet name



please provide code

 

[mumps]

Do you enter the value in cell B3 manually or is it the result of a formula? In which column of the search sheet do you want the sheet name?

 

[atuljadhavnetafim]

Do you enter the value in cell B3 manually or is it the result of a formula? In which column of the search sheet do you want the sheet name?

Yes, I will add value in cell B3 manually and column E need to be search in all sheet

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Search" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in B3 and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet, val As Range, x As Long: x = 8
    For Each ws In Sheets
        If ws.Name <> "Search" Then
            Set val = ws.Range("E:E").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not val Is Nothing Then
                val.EntireRow.Copy Cells(8, 1)
                x = x + 1
            End If
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[atuljadhavnetafim]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Search" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in B3 and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet, val As Range, x As Long: x = 8
    For Each ws In Sheets
        If ws.Name <> "Search" Then
            Set val = ws.Range("E:E").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not val Is Nothing Then
                val.EntireRow.Copy Cells(8, 1)
                x = x + 1
            End If
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

Hi,

thanks for your codes, but it is not working

 

[mumps]

How is it not working? Do you get an error message and if so, what is the message and which line of code is highlighted when you click "Debug"?

 

[atuljadhavnetafim]

How is it not working? Do you get an error message and if so, what is the message and which line of code is highlighted when you click "Debug"?

As when i click on macro then there is no macro show in sheet, when in enter value in B3 and press enter then nothing is happen.

 

[mumps]

The macro is a Worksheet_Change event which means that it is in the code module foe the worksheet. To see the macro, do the following: right click the tab name for the sheet and click "View Code". A window will open showing you the macro. This type of macro is triggered automatically when you change or add a value in a cell. When you enter a value in B3 and exit the cell, the macro will run automatically. If it doesn't work, it's difficult to say why without seeing your file. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[atuljadhavnetafim]

Please find link,

https://www.dropbox.com/s/nivr08f2br0odce/Test.xlsm?dl=0

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet, val As Range
    Sheets("Search").Range("B10").Resize(4, 6).ClearContents
    For Each ws In Sheets
        If ws.Name <> "Search" Then
            Set val = ws.Range("E:E").Find(Target.Value, LookIn:=xlFormulas, lookat:=xlWhole)
            If Not val Is Nothing Then
                ws.Cells(val.Row, 1).Resize(1, 6).Copy Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
            End If
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub