Search from All sheet

atuljadhavnetafim

Active Member
Joined
Apr 7, 2012
Messages
341
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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?
 
Upvote 0
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
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"?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Try:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,956
Messages
6,175,616
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top