Find matches in different spreasheets then copy and paste to new s

[newlearner01]

Hi,

I am a new in VBA and I am wondering whether someone can help me?

My workwork has 45 sheets. I would like to create a macro which will search
for string value in the second column ("B") of the first 41 sheets and if it
finds the matches it will copy entire row and paste to new sheet in same
workbook.

Thanks in advance.

 

[Don Guillett]

what kind of value, how many possible matches per worksheet? Is the new
sheet already there or does the macro need to create it?, etc..............

 

[Rich Locus]

Hello:

Before you run this, create a 46's worksheet which will be the target of
your paste. Of course, you can change the code to give it a name instead of
just a number.

This will scan column B of each of the first 41 worksheets and paste the row
for any matches to column B (you will need to determine where you want the
string to come from - I just hard-coded an example string of
"WhatImLookingFor").

This structure should give you a start.

Option Explicit
Option Base 1

Public Sub CreateNewSheetOnMatchingValues()
Dim intSheetNumber As Integer
Dim lngScanForTextRow As Long
Dim lngRowsInColumnB As Long
Dim lngSheet46RowCount As Long

lngSheet46RowCount = 0

' **********************************************
' Scan Column B of all 41 worksheets for a match
' **********************************************
For intSheetNumber = 1 To 41
Sheets(intSheetNumber).Select
'
*******************************************************************************
' When A Sheet Is Selected, Determine the Number of Rows in the Sheet in
Column B
'
*******************************************************************************
lngRowsInColumnB = Cells(Rows.Count, "B").End(xlUp).Row
'
*******************************************************************************
' Scan All the Items in Column B for "WhatImLookingFor" And Paste In Sheet 46
'
*******************************************************************************
For lngScanForTextRow = 1 To lngRowsInColumnB
If Cells(lngScanForTextRow, 2).Value = "WhatImLookingFor" Then
'
**********************************************************************************
' If you find a match, copy the row, switch to sheet 46, paste, and then
switch back
'
**********************************************************************************
Rows(lngScanForTextRow).Select
Selection.Copy
Sheets(46).Select
lngSheet46RowCount = lngSheet46RowCount + 1
Cells(lngSheet46RowCount, 1).Select
ActiveSheet.Paste
Range("A1").Select
Sheets(intSheetNumber).Select
Application.CutCopyMode = False
Range("A1").Select
End If
Next lngScanForTextRow
Next intSheetNumber

Sheets(1).Select
Range("A1").Select

End Sub

 

[Mike H]

Hi,

try this

Sub copyrows()
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Results"
Dim x As Long
Dim LastRow As Long
Dim MyValue As String
Dim CopyRange As Range
MyValue = "Somestring"
For x = 1 To 41
Set sht = Sheets(x)
LastRow = sht.Cells(Rows.Count, "B").End(xlUp).Row
Set myrange = sht.Range("B1:B" & LastRow)
For Each C In myrange
If UCase(C.Value) = UCase(MyValue) Then
If CopyRange Is Nothing Then
Set CopyRange = C.EntireRow
Else
Set CopyRange = Union(CopyRange, C.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
LastRow = Sheets("Results").Cells(Rows.Count, "A").End(xlUp).Row + 1
CopyRange.Copy Destination:=Sheets("Results").Range("A" & LastRow)
Set CopyRange = Nothing
End If
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rich Locus]

Mike:

Very nice use of advanced features. Could you provide me with the proper
"type" for the following variables:

Dim sht
Dim myrange
Dim C

They were not included in the example.

 

[newlearner01]

There are less than 20 possible matches per sheet, and I need to create new
sheet. The value is text.

Thank for asking.

 

[newlearner01]

Thanks. It works. Your instructions ave very clear. You can be a great
instructor

 

[newlearner01]

Thanks. As Rich said, very nice use of advanced features. Both codes work,
but yours runs very fast.

 

[Rich Locus]

Mike:

Love the speed of the copy solution for this post. Do you know how to just
copy the values and not the formatting? The copy statement in your example
copies everything, including formatting and cell colors. Is there a way to
copy that works more like a "Paste Special, values only?"

 

[Mike H]

Dim sht as object

Dim myrange as range
Dim C as range

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[newlearner01]

Yes, it will be great if we have that addition option.

BTW, I would like to add the reference sheet names in the column A, and the
copied rows will start from column B of new sheet ("result" in your example)
so that we know from where those copied rows come. How can I add sheet names
to the Set CopyRange statement?

Thanks.

 

[Rich Locus]

Hello again

Here's a minor tweak to the code from Mike H that does a Paste Special for
values only and doesn't change colors, fonts or anything else:

Option Explicit
Sub copyrows()
Dim x As Long
Dim LastRow As Long
Dim MyValue As String
Dim CopyRange As Range
Dim sht
Dim myrange As Range
Dim C

' Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Results"

MyValue = "WhatImLookingFor"
For x = 1 To 2
Set sht = Sheets(x)
LastRow = sht.Cells(Rows.Count, "B").End(xlUp).Row
Set myrange = sht.Range("B1:B" & LastRow)
For Each C In myrange
If UCase(C.Value) = UCase(MyValue) Then
If CopyRange Is Nothing Then
Set CopyRange = C.EntireRow
Else
Set CopyRange = Union(CopyRange, C.EntireRow)
End If
End If
Next

If Not CopyRange Is Nothing Then
LastRow = Sheets("Results").Cells(Rows.Count, "A").End(xlUp).Row + 1
CopyRange.Copy
Sheets("Results").Select
Cells(LastRow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Cells(1, 1).Select
Set CopyRange = Nothing
End If
Next
Sheets(1).Select
Cells(1, 1).Select

End Sub

 

[Rich Locus]

Here's another change that allows the rows from the first 41 spreadsheets
(only the first 30 columns are copied, but you can change that to make it
bigger or smaller) to be copied to Column B of results, with the name of the
spreadsheet in Column A:

Sub copyrows()
Dim x As Long
Dim LastRow As Long
Dim MyValue As String
Dim CopyRange As Range
Dim sht
Dim myrange As Range
Dim C

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Results"
MyValue = "WhatImLookingFor"
For x = 1 To 41
Set sht = Sheets(x)
LastRow = sht.Cells(Rows.Count, "B").End(xlUp).Row
Set myrange = sht.Range("B1:B" & LastRow)
For Each C In myrange
If UCase(C.Value) = UCase(MyValue) Then
If CopyRange Is Nothing Then
Set CopyRange = Range(C, C.Offset(0, 30))
Else
Set CopyRange = Union(CopyRange, Range(C, C.Offset(0, 30)))
End If
End If
Next

If Not CopyRange Is Nothing Then
LastRow = Sheets("Results").Cells(Rows.Count, "B").End(xlUp).Row + 1
CopyRange.Copy Destination:=Sheets("Results").Range("B" & LastRow)
Sheets("Results").Cells(LastRow, 1).Value = sht.Name
Set CopyRange = Nothing
End If
Next
End Sub

 

[newlearner01]

Thanks again Rich

It's interesting that this version finds more matches than Mike's original
code. Somehow, Mike's code ignores hidden rows, and the rows whose first
columns are empty. However, yours does not copy/paste first column of the
existing sheets. How can we modify code to fix it? Can you please show me how
to modify code to search in any column (ex: column 1 or 10)?

Also, both codes ignore several matches in a few spreadsheets. When I use
Find tool, Excel does overlook those matches too. Is that a bug in Excel? I’m
using Excel 2007.

 

[Rich Locus]

OK... This code copies the entire row from Column A to Column F and Does a
Paste in Column B of Results, allowing the sheet name to be in Column A.
Hopefully the notes will make sense to you.

Option Explicit
Sub copyrows()
Dim x As Long
Dim LastRow As Long
Dim MyValue As String
Dim CopyRange As Range
Dim sht
Dim myrange As Range
Dim C

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Results"
MyValue = "WhatImLookingFor"
For x = 1 To 41
Set sht = Sheets(x)
' ***********************************************
' Find The Last Row with Data in Column B
' ***********************************************
LastRow = sht.Cells(Rows.Count, "B").End(xlUp).Row

' ***********************************************
' Set myrange to Be Column B Data Up to Last Row
' ***********************************************
Set myrange = sht.Range("B1:B" & LastRow)

' *****************************************************
' Loop Through Every Cell in myrange (All Column B Data)
' *****************************************************
For Each C In myrange
If UCase(C.Value) = UCase(MyValue) Then
' *******************************************************
' C References The Current Row's Cell in Column B:
' C.Offset(0, -1) Says Reference The Cell in Column A, Same Row
' C.Offset(0, 30) Says Reference The Cell in Column AF, Same Row
' So the Range Statement Says Copy the Current Row
' From Column A Through AF
' *******************************************************
If CopyRange Is Nothing Then
Set CopyRange = Range(C.Offset(0, -1), C.Offset(0, 30))
Else
Set CopyRange = Union(CopyRange, Range(C.Offset(0, -1),
C.Offset(0, 30)))
End If
End If
Next

If Not CopyRange Is Nothing Then
LastRow = Sheets("Results").Cells(Rows.Count, "B").End(xlUp).Row + 1
CopyRange.Copy Destination:=Sheets("Results").Range("B" & LastRow)
Sheets("Results").Cells(LastRow, 1).Value = sht.Name
Set CopyRange = Nothing
End If
Next
End Sub

 

[newlearner01]

Thanks Rich. I appreciate your detailed instructions and your patient