VBA Code Needed

ITstudent2006 · Jan 4, 2011, 08:02 AM

There is a bandaid approach to this if you weren't aware.

1. Right-Click Sheet 1999
2. Click Select All Sheets (this puts all sheets in group mode)
3. CTRL+F
4. Input Name

*This will search all sheets for this name and show you the sheet it's on, if there are multiple instances it will start with the first sheet and go through to the last bringing up all occurrences of that name. Create a Macro if needed!

Not sure if this is anyhelp as I am sure you know this already, but that's all I can offer.

JBeaucaire · Jan 5, 2011, 01:20 AM

A quick macro to collect the string rows with the string you enter onto the sheet called Find:

Code:

Option Explicit

Sub FindCopy()
Dim ws As Worksheet
Dim MyStr As String
Dim nmFind As Range
Dim nmFirst As Range

MyStr = Application.InputBox("Name to find and copy:", "Enter Name", Type:=2)
If MyStr = "False" Or MyStr = "" Then Exit Sub
On Error Resume Next

For Each ws In Worksheets
    If ws.Name <> "Find" Then
        Set nmFind = ws.Range("E:E").Find(MyStr, LookIn:=xlValues, LookAt:=xlWhole)
        If Not nmFind Is Nothing Then
            Set nmFirst = nmFind
            Do
                nmFind.EntireRow.Copy Range("A" & Rows.Count).End(xlUp).Offset(1)
                Set nmFind = ws.Range("E:E").FindNext(nmFind)
            Loop Until nmFind.Address = nmFirst.Address
            Set nmFind = Nothing
            Set nmFirst = Nothing
        End If
    End If
Next ws

End Sub

dannac · Jan 5, 2011, 04:46 AM

ITstudent2006 ..... I was not aware of that shortcut and it works well and will be very helpful.
Thank You.

JBeacaire ...... Wow, this code works perfectly.
What a time-saver this will be. Thanks so much.

Will also help me learn more about VBA ......
As I decipher your code with my " VBA Idiot Book ".

Very helpful .... Thanks

JBeaucaire · Jan 6, 2011, 04:22 AM

Things to read up on to fully decode the macro:

Dimensioning variables
Application.InputBox method (as opposed to just InputBox method which has less controls)
The .Find function
The .FindNext function
Use of a For/Next loop to run like commands repetitively, in this instance on each worksheet
Use of a Do/Loop to run like commands repetitively until some criteria is achieved to break the loop, in this instance if we find the string in a cell we've already found it in once already.

dannac · Jan 6, 2011, 09:39 AM

Thanks for the extra info.

Visited your site ....... Have a lunch on me.

JBeaucaire · Jan 8, 2011, 06:05 AM

Very kind of you! I will treat the wife this weekend.

Hope you find lots of useful tips/code on the site, too, that's what it's really for. Let me know if you think of something you think I should include as a general macro/function.

Thanks again.

dannac · Jan 27, 2011, 11:01 AM

Code:

Option Explicit

Sub FindCopy()
Dim ws As Worksheet
Dim MyStr As String
Dim nmFind As Range
Dim nmFirst As Range

MyStr = Application.InputBox("Name to find and copy:", "Enter Name", Type:=2)
If MyStr = "False" Or MyStr = "" Then Exit Sub
On Error Resume Next

For Each ws In Worksheets
    If ws.Name <> "Find" Then
        Set nmFind = ws.Range("F:F").Find(MyStr, LookIn:=xlValues, LookAt:=xlWhole)
        If Not nmFind Is Nothing Then
            Set nmFirst = nmFind
            Do
                nmFind.EntireRow.Copy Range("A" & Rows.Count).End(xlUp).Offset(1)
                Set nmFind = ws.Range("F:F").FindNext(nmFind)
            Loop Until nmFind.Address = nmFirst.Address
            Set nmFind = Nothing
            Set nmFirst = Nothing
        End If
    End If
Next ws

End Sub

The code above works fine as for pulling in the row that contains the name from the input box.

But I've noticed it brings in the first instance only ... would need it to bring in all instances of that name.

Some sheets may have no names ... some one name ... some four names, etc ....

Is this a hard fix ?

JBeaucaire · Jan 28, 2011, 10:32 AM

The code includes DO loop that copies each row found to the next empty row on the FIND sheet. I've used it, so it's the correct syntax.

If you're not getting that behavior with your workbook, I'd need to see it to spot was was causing other rows to be ignored.

One suspect is hidden/unseen spaces in the cells that we're matching to. These strings are not the same:

Code:

"John Doe"
"John  Doe"
"John Doe "

dannac · Jan 28, 2011, 12:32 PM

Thanks for the explanations to both my post.

Looking back at the code for this one ....

I had changed the lookup column in the top part of the code, and did not make the same adjustment in the bottom part of the code.

Works like it should .... Thank you.

VBA Code Needed

Add your answer here.

Check out some similar questions!