Search multiple worksheets for search term and paste results in a summary sheet

[Andy15]

Hi All,
I am hoping somebody can help with a some coding that I'm trying to find.

I have limited VBA knowledge but willing to try and learn.

I have a workbook with multiple worksheets, the worksheets contain columns of data, the data can be in different columns on different worksheets and what I want to do is create a popup message box asking what to search for, then copy the resulting columns to either a summary sheet, a new sheet with the same name as the search term or a new workbook.

The resulting summary page needs to include columns A and B and then the search data is required to be pasted in subsequent columns.

The workbook will be updated weekly with additional information so ideally I could execute the search term again and the code will clear all of the summary results and paste the new results.

I hope this is enough information. I have attached a spreadsheet with 3 tabs of data and a summary sheet. The search term would have been Tom in this instance.

Thanks in advance
Andy15


Book1.xlsm

[Raphaelp]

You can find good search code with a little searching :

https://www.mrexcel.com/forum/excel-...ml#post1644164

[Andy15]

Hi Raphaelp,

Thanks for your reply but that page just creates a pup up box telling me how many occurences the search term has found. I have spent many hours searching different websites but none of them seem to copy columns to a new sheet, they all seem to be rows and although I have tried modifying vba code I can't seem to find a solution.

[Andy15]

Hi I have made some headway by searching for code and making some mods. I can search for a term within multiple sheets via an input box and have the results pasted to a sheet named Search within the same workbook. I need to copy the column which has the search term in it (say helen) and also copy columns A and B which I have also managed to do.

Please can somebody help me to paste the results to a new workbook which I want the macro to create automatically instead of to a sheet named Search

I have uploaded an example workbook to show how far I have got.

Here is a copy of my code.

Option Explicit
Option Compare Text '< ignore case
'
Sub Searchcolumns()
'
Dim FirstAddress As String, WhatFor As String
Dim Cell As Range, Sheet As Worksheet
'

With Application
.ScreenUpdating = False
.EnableEvents = False
End With


WhatFor = InputBox("What are you looking for?", "Search Criteria")
If WhatFor = Empty Then Exit Sub
'
For Each Sheet In Sheets
If Sheet.Name <> "SEARCH" Then
With Sheet.Rows(2)
Set Cell = .Find(WhatFor, LookIn:=xlValues, LookAt:=xlPart)
If Not Cell Is Nothing Then
FirstAddress = Cell.Address
Do

Sheet.Range("A1").EntireColumn.Copy Destination:=Sheets("SEARCH").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
Sheet.Range("B1").EntireColumn.Copy Destination:=Sheets("SEARCH").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)

Cell.EntireColumn.Copy Destination:=Sheets("SEARCH").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)

Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End If
End With
End If
Next Sheet
'
Set Cell = Nothing

'AutoFit All Columns on Worksheet
ThisWorkbook.Worksheets("Search").Cells.EntireColumn.AutoFit

End Sub




Thanks in advance.

[sintek]

Hi Andy15

Your post does not conform with rule3 of the forum. please apply so that we are able to assist.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window

[sintek]

Andy, this should get you started...Providing that you have an existing sheet called Summary

Please Login or Register  to view this content.

[Logit]

Deleted comments .......

[sintek]

Hi Andy

Herewith the amended code with message pop up to clear sheet.

Please Login or Register  to view this content.

[Andy15]

Thanks for your help Sintek

I have the following code that is working fine and it copies the relevant data to a new workbook.

I wonder if somebody can help modify the code to have a pop up asking for the name of an existing workbook for the data to be copied to.

Also is there a way to also have a pop up to select s specific sheet within the workbook rather than sheet 1

Please Login or Register  to view this content.

Thanks

[sintek]

Hi Andy15

This enquiry ....

can help modify the code to have a pop up asking for the name of an existing workbook for the data to be copied to.

Does the info not go into the summary sheet. What sheets are available for the user to choose from. It is very easy to apply a pop up i.e

Please Login or Register  to view this content.

What do you mean with this....

is there a way to also have a pop up to select s specific sheet within the workbook rather than sheet 1

[Andy15]

Hi Sintek,

Sorry if my explanation is not very clear. I will try and explain little better.

I have a workbook with multiple sheets. The data i want to extract and copy is working within the code I have published but at the moment it pastes it ti a new workbook named Book1 and pastes the date into the first sheet of that workbook (sheet1)

I am trying to paste the data into a workbook of my choice by using a popup to select the workbook name. The other thing I am trying to do is copy the data to a specific sheet within that workbook.
e.g. I want to open workbook (Personnel) and then copy the data to sheet (helen) within that workbook. The workbook name and sheet name will need to be selected by way of a pop up as they will be different depending on search term.

Thanks

[sintek]

Do those books and sheets exist or must they be created by code

[Andy15]

I can create them as they will be updated on a regular basis so only need to be created once.

[sintek]

okay, so as per your original sample upload with Sheets 1701,1703,1705 & Summary
The information is going to be copied from the 1701,1703, 705 etc sheets NOT TO SUMMARY but to a different workbook entirely to a specific sheet name.....And his workbook would already be set up and open?

[sintek]

So as example if you input Tom, the code must loop through the sheets 1701,1703,1705 etc and you want to
1. Choose via input what workbook and sheet within that workbook to paste the info to
2. Must that workbook have the option of clearing all before pasting into.

[Andy15]

Yes if possible

[Andy15]

Sorry, yes if possible but only the sheet that is being populated.

[sintek]

Tell me Andy15

The workbook with sheets 1701,1703,1705 etc .....Is this always going to be the main workbook and if so what is it called.

[sintek]

Hi Andy

Right, I've got it working as per my understanding...
Herewith amended code. I have also uploaded two worksheets for this exercise. What I'd like you to do is Open Andy.xlsm and on Sheets("Summary") Run the code... Your Workbook to save to input must be [Personnel] and the choice of sheets is Tom or Helen or Bob.....Has been set up for this exercise purpose. remember to put both workbooks on your desktop and change the path..... this part of code below to your path...The code will open the workbook automatically. Have not yet added close code....

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Andy15]

Hi Sintek.

I can only say "Wow" that is amazing.

Programme is exactly what I was looking for.

You are a star

Thanks
Andy

[sintek]

Pleasure Andy15 Glad I could assist.

[sintek]

Hi Andy15

If this is so...

Programme is exactly what I was looking for.

please mark thread as solved and add to reputation by clicking star...

[Andy15]

Hi Sintek,

How do I mark a solved

[Andy15]

Found it