|
|
|
|
Full Member
|
|
Jan 4, 2011, 08:48 AM
|
|
VBA Code Needed
Workbook with sheets from 1999 to 2010.
Columns filled to H... Rows varies, but approx 50 each sheet.
Client "last name" in column E.
Will insert a sheet called "Find" and insert a form button.
Clicking button would open InputBox for name to search.
VBA code would search sheets (Col E) and if name from input box is found
The entire row from that particular sheet would be inserted on the "Find" sheet
... and there may be more than one instance of that particular name.
|
|
|
Networking Expert
|
|
Jan 4, 2011, 09: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.
|
|
|
Software Expert
|
|
Jan 5, 2011, 02: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
|
|
|
Full Member
|
|
Jan 5, 2011, 05: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
|
|
|
Software Expert
|
|
Jan 6, 2011, 05: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.
|
|
|
Full Member
|
|
Jan 6, 2011, 10:39 AM
|
|
Thanks for the extra info.
Visited your site... have a lunch on me.
|
|
|
Software Expert
|
|
Jan 8, 2011, 07: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.
|
|
|
Full Member
|
|
Jan 27, 2011, 12:01 PM
|
|
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 ?
|
|
|
Software Expert
|
|
Jan 28, 2011, 11: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 "
|
|
|
Full Member
|
|
Jan 28, 2011, 01: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.
|
|
Question Tools |
Search this Question |
|
|
Check out some similar questions!
VBA Code for creating multiple worksheets
[ 11 Answers ]
Hey guys -
Ok, so I found some code on the Internet that I have been trying to tweak to my liking but I'm having some trouble. Here's what I am trying to do.
I want to create a worksheet for each day of a given month and name the worksheet for each respective day. The naming convention...
VBA Code for Creating a Chart
[ 1 Answers ]
Ok, here's my problem.
I have built a macro that creates a chart for me from certain columns of data. The columns are always the same ones and the data I contemplate always begins in the same row but the end data is a variable. Also, the last row always contains a Totals Row which is something...
VBA code
[ 2 Answers ]
Once the month ends, cell "A1" must tick over to zero, where "A1" is used by me as a counter to show how much has been deliverd to date for the month.
VBA Code to Save from Microsoft Outlook
[ 5 Answers ]
Hello -
I hope my problem is a simple one. What I am attempting to do is create a VBA macro that will allow me to select an e-mail from my Outlook Inbox and save that e-mail (as an Outlook item) to a specified drive—C:\Outlook Items\
I cannot record a macro in Outlook so I am stumped as to...
View more questions
Search
|