Using VBA to search over multiple sheets and paste data into new one

[drabbit]

Hello,
Im new in VBA, and im searching for a script that would search in multiple sheets within one woorkbook, for specific text (specified in script itself) and paste it in a new sheet. for a descritpion:
- multiple sheets (search in all of them)
- skip new reference sheet + few others sheets - for example sheet3 and sheet4
- in all sheets i've got names and number of hours next to them, I need to look for specific name within all sheets and once there copy whole row with it.
- if name is within sheet copy cell value B12 and C13 as well within same row in output sheet.

I've been searching for something like this within threads,and i found similarities but as I started this week i was not able to pull together exactly what I need from several scripts. Thank you for every answer.

 

[drabbit]

some additional informations
- pull specific name with hours from whole workbook, into new sheet, into column (in new sheet new row name+hours)
f.e:
smith 1,5h (sheet2) + cell B12 and C13 from same sheet
smith 2h (sheet5) + cell B12 and C13 from same sheet
smith 4,5h (sheet6) + cell B12 and C13 from same sheet

 

[My Aswer Is This]

Well since you have not received a answer yet I will take a look at it.
Specific details are always important

Like can we have a Input box pop up asking for what value to look for?
Or what is your ideal.
And on each sheet in what column should we look for the search value.
Say something like column(5)
Do not say column Data

Now if we search column D and find the search value we take that value and the value in column D and do what with it?
Copy it to new sheet named "Alpha" and put where?
See we need specific details like this.
And we want to search all sheets in the workbook?
If not give the name or names of the sheets to search

 

[drabbit]

Hello,

Thanks for quick reply

- preferably have the script hard written for each name running from macro option
- names are stored in column "A"
- once name found in each worksheet, copy and paste in into new worksheet (lets say "Alpha") column A - name B- hours (same row) column C - cell B12 - D - cell C13, for each match in every sheet
- search every sheet except sheet3 and sheet4

 

[My Aswer Is This]

Hello,

Thanks for quick reply

- preferably have the script hard written for each name running from macro option
- names are stored in column "A"
- once name found in each worksheet, copy and paste in into new worksheet (lets say "Alpha") column A - name B- hours (same row) column C - cell B12 - D - cell C13, for each match in every sheet
- search every sheet except sheet3 and sheet4

Not sure I understand this means.
Do you mean search for the values found in sheet named master Column 1
And search where on each sheet? Search column B?
Not sure what this means:
column C - cell B12 - D - cell C13,

 

[drabbit]

Search in whole workbook with multiple sheets (except new one with results, and sheet3 and sheet4)
search for "name" in column A
if there within a sheet copy whole row + cells B12 and C13 from each sheet the name is in
so the result will be:
A B C D
Smith 1,5h town project name

 

[drabbit]

result would be something like this

 

[My Aswer Is This]

Still not sure what we are search for and search each column B on each sheet except of sheet3 and sheet4

Please say something like this:

For each sheet in the workbook column B for: What are you saying search for one specific value or all the values in sheet master column A

Like I do not know what this means:
for each name running from macro option
- names are stored in column "A"

 

[drabbit]

almost every sheet is a project sheet with specific project name and people assign to it.
so i would like to search every sheet in workbook for name "smith", names are always in column A in every sheet. Copy and paste it from every sheet into a new sheet

pull out name "smith" from every sheet in workbook, except specific sheets (3&4) and paste it into new one row by row.

 

[My Aswer Is This]

Try this script: You will get a Input Box asking to enter search value.
I have it set to default "Jane" Just change that to value to search for.

Sub Filter_Me_Please()
'Modified  10/4/2021  5:31:44 PM  EDT
Application.ScreenUpdating = False
Dim lastrow As Long
Dim lastrowa As Long
Dim counter As Long
Dim ns As String
ns = "New Sheet"
Dim C As Long
Dim s As Variant
C = 1 ' Column Number Modify this to your need
Dim i As Long
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ns
s = InputBox("Enter Value to search", , "Jane")

For i = 1 To Sheets.Count - 1
            lastrow = Sheets(i).Cells(Rows.Count, C).End(xlUp).Row
            
            With Sheets(i).Cells(1, C).Resize(lastrow)
                
                lastrowa = Sheets(ns).Cells(Rows.Count, C).End(xlUp).Row + 1
                    .AutoFilter 1, s
                        counter = .Columns(C).SpecialCells(xlCellTypeVisible).Count
                            If counter > 1 Then
                                .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets(ns).Cells(lastrowa, 1)
                                    Else
                                        MsgBox "The value " & s & " Not Found On sheet named " & vbNewLine & Sheets(i).Name & vbNewLine & "Click OK and we will Continue"
                                        
                                            End If
                                             .AutoFilter
            End With
        
            Next
Application.ScreenUpdating = True
End Sub