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

drabbit

New Member
Joined
Oct 4, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

drabbit

New Member
Joined
Oct 4, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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-known Member
Joined
Jul 5, 2014
Messages
18,150
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 4, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
18,150
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Oct 4, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 4, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

result would be something like this

1633373186059.png
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,150
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 4, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

1633374363985.png

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

Well-known Member
Joined
Jul 5, 2014
Messages
18,150
Office Version
  1. 2013
Platform
  1. Windows
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.
VBA Code:
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
 

Forum statistics

Threads
1,147,735
Messages
5,742,869
Members
423,760
Latest member
photogfrog

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
Top