PDA

View Full Version : Solved: Search multiple sheets and copy results to another sheet



Shaolin1976
05-06-2006, 03:24 AM
Hi,

Firstly, many thanks to Tecnik for the helpful links in http://vbaexpress.com/forum/showthread.php?t=7910

I have been trying to modify a macro found on one of Tecnik's links which searches a column in the activesheet and copies the results to an different sheet.

I have a search sheet (imaginatively named "SEARCH") and numerous other sheets (named 1 through to 100) which contain the data which is to be searched. I would like to have the macro (located on a button in the "SEARCH" sheet) search column A in all other sheets and paste each row where it finds a match into the SEARCH sheet.

I have been trying to get this to work for just one sheet (Sheet 88) to start with and then go from there to make it search all sheets but I keep geting an error on Line 20 and I really haven't a clue as to why. Any help as to why the error keeps occuring and also how I'd go about making it search all other sheets in the workbook would be greatly appreciated. Ideally I would like to have an input box from which I can enter the value to be searched but thats only if the following code works without throwing up an error first!

Public Sub SearchButton_Click()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 2
LSearchRow = 2

'Start copying data to row 2 in SEARCH (row counter variable)
LCopyToRow = 2

While Len(Sheets("88").Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column A = "Test", copy entire row to SEARCH
If Sheets("88").Range("A" & CStr(LSearchRow)).Value = "Test" Then

'Select row in Sheet 88 to copy
Sheets(88).Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into SEARCH in next row
Sheets("SEARCH").Select
Sheets("SEARCH").Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet 88 to continue searching
Sheets("88").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Sheets("SEARCH").Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End SubMany thanks

Shaolin

johnske
05-06-2006, 07:50 AM
Try this...

Option Explicit
Option Compare Text '< ignore case
'
Sub SearchSheets()
'
Dim FirstAddress As String, WhatFor As String
Dim Cell As Range, Sheet As Worksheet
'
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.Columns(1)
Set Cell = .Find(WhatFor, LookIn:=xlValues, LookAt:=xlPart)
If Not Cell Is Nothing Then
FirstAddress = Cell.Address
Do
Cell.EntireRow.Copy _
Destination:=Sheets("SEARCH").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End If
End With
End If
Next Sheet
'
Set Cell = Nothing
End Sub

Shaolin1976
05-06-2006, 08:15 AM
Hi Johnske,

Thanks for your reply, your code works fine in that it searches the other sheets but it only throws up the first match. It stops once it has found the first match; it doesnt loop through the rest of the hits on that sheet and subsequent sheets and paste them all to the SEARCH sheet.

Many thanks again

Shaolin

lucas
05-06-2006, 08:23 AM
it does search subsequent sheets but it does stop at the first one it finds on each sheet....

Shaolin1976
05-06-2006, 08:26 AM
I just played about with it a little more and yes, it does copy the first match from each sheet but not multiple matches from each sheet. Is there an easy fix to make it find each and every match on each sheet?

Shaolin1976
05-06-2006, 08:32 AM
EDIT: Sorry, ignore this post

johnske
05-06-2006, 08:42 AM
Sorry, my oops, a line was accidentally deleted - this'll work...


Option Explicit
Option Compare Text '< ignore case
'
Sub SeachSheets()
'
Dim FirstAddress As String, WhatFor As String
Dim Cell As Range, Sheet As Worksheet
'
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.Columns(1)
Set Cell = .Find(WhatFor, LookIn:=xlValues, LookAt:=xlPart)
If Not Cell Is Nothing Then
FirstAddress = Cell.Address
Do
Cell.EntireRow.Copy _
Destination:=Sheets("SEARCH").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End If
End With
End If
Next Sheet
'
Set Cell = Nothing
End Sub

Shaolin1976
05-06-2006, 08:47 AM
That works PERFECTLY, many thanks Johnske, this is going to save me quite a bit of time searching through endless columns. I sincerely appreciate your help.

Now to try and figure out why your code works heh

Shaolin

lucas
05-06-2006, 08:54 AM
Thats slick and handy John, add it to the kb?

lucas
05-06-2006, 09:00 AM
John, if you wanted to search for cells with an exact string this can be changed:

Set Cell = .Find(WhatFor, LookIn:=xlValues, LookAt:=xlPart)
to this

Set Cell = .Find(WhatFor, LookIn:=xlValues, LookAt:=xlWhole)
correct?

johnske
05-06-2006, 09:05 AM
Thats slick and handy John, add it to the kb?Not at this stage Steve. Might wait for the next KB contest, I've got forty or fifty in hand for that :)

Yeh, can easily make it xlWhole if wanted, but ppl are usually satisfied with a partial match

lucas
05-06-2006, 09:07 AM
I'll be yoinking it and using it in the meantime..Thanks