Search Data from Multiple Sheets and copy found data to new workbook

**xenith1988** · 06-26-2012, 02:00 AM

I would attach the .xls's here but they are both 5.4MB's big (avg of 2.5 million cells). They are the schedules of where my father has worked for the last 3 years at a company. They have included everyone on the schedule (not just him). I need to find his name on each sheet between both workbooks (I'll run the macro two times if I need to. Once for each workbook), copy the whole row, then paste it into a NEW workbook. (Each workbook has 69 slides). Now, I have tried using some VGA scripts I came across but nothing seems to do the trick... Maybe I am just retarded in this area. I'm seriously biting the bullet at this point and nervous that I will not complete on time.

Some specifics: The column I will be searching in will be the F or G column; The name will be typed out as; Mr. Bobby , Mr Bobby, or MR BOBBY. Umm anything more just ask. I'm probably not be able to sleep tonight unless I complete this anyways, so I'll be here periodically.

-Timothy X

I found ONE script that seems to be in the area of what I am looking for ... but I edited the values and it seems to not work. (After it is finished it says 0 rows copied!) I am green behind the ears but not incompetent. God, I hope one of you guys can help me soon.

Please Login or Register  to view this content.

**Pepe Le Mokko** · 06-26-2012, 02:18 AM

To make sure it will be solved on time, perhaps contact a pay site

**xenith1988** · 06-26-2012, 02:18 AM

I been going over a couple other posts and come across things mentioning "Proper headers" or "Autofilter" I know (to a degree) what Autofilter is. I don't know EXACTLY how it works but I do know how to select the whole table and click tools>filter>autofilter. This would not be necessary would it? I don't mind either way, just pointing out potential user problems. I'm a bit ignorant in this area but I will obviously need VBA/Macro help to finish this project.

**xenith1988** · 06-26-2012, 02:23 AM

I was really hoping to avoid the whole payment ideology seeing as this website DOES exist. I'll be here till 2 PM without sleep. I got plenty of coffee and food to keep myself awake and alert as I continue on this project. I'm still trying different scripts and checking the results of them. (Nothing seems to be working right though, so I'm starting to the idea I am doing something wrong.)

**xenith1988** · 06-26-2012, 02:32 AM

*UPDATE*

I copied ONE Sheet that contained the value I was looking for and ran the macro in a separate workbook. It ran fine, copied the rows into a new SHEET. I think the issue lies somewhere in the fact that I have 69 SHEETS in the original workbook so when the macro runs it doesn't create a new sheet... (or sheet 2 is occupied) or something to that sort.

PS: Or is it because the script runs on just the active sheet? I ran it again in the original workbook and it copied 30 rows but pasted it on the first sheet, replacing the first 30 rows with the copied information. Am I asking for something that isn't possible?

<I need the search function to go through the whole WORKBOOK, not the SHEET, I am unfamiliar with the syntax to edit the code myself.>

**JBeaucaire** · 06-26-2012, 02:59 AM

As per forum rules, please EDIT post #1 and correct the thread title, it should depict the topic explicitly. Search Data from Multiple Sheets , something like that.

I'll take a look at your macro in the meantime.

**xenith1988** · 06-26-2012, 03:21 AM

Originally Posted by JBeaucaire

As per forum rules, please EDIT post #1 and correct the thread title, it should depict the topic explicitly. Search Data from Multiple Sheets , something like that.

I'll take a look at your macro in the meantime.

Edited as per request. (I searched for forum rules and haven't exactly found anything... sorry for busting any I wasn't aware of... ... haha I'll keep looking for forum rules)

PS: I sent you a PM JBeaucaire. Not sure how the notifications work for this forum in-comparison to other forums, but I thought I'd let you know.

**galvinpaddy** · 06-26-2012, 03:39 AM

Perhaps you could load the file using box.com and post the link, once your thread title has been sorted mind

**xenith1988** · 06-26-2012, 04:00 AM

Originally Posted by galvinpaddy

Perhaps you could load the file using box.com and post the link, once your thread title has been sorted mind

https://www.box.com/s/2ed3fa8b16fc62d1cf89
https://www.box.com/s/ce6a780707900bdf3a2a

Should be the two files on www.box.com

Let me know if there is anything else I should do.

*EDIT*
These have been removed, they don't seem to be working properly.

**xenith1988** · 06-26-2012, 04:31 AM

Can someone let me know if those links work or not? I'm stuck to one IP addy and one computer... so self verification isn't exactly an option for me. *Appreciated

**xenith1988** · 06-26-2012, 05:24 AM

I hate bumping.

**arlu1201** · 06-26-2012, 06:02 AM

You do not need to provide your original file. All we need to see is how your data is structured so we can help you quickly. Even a sample file with the same format as your original will do.

**xenith1988** · 06-26-2012, 06:04 AM

Should I make a sample file?

**arlu1201** · 06-26-2012, 06:05 AM

I am not able to download your file. It will be better if you create one and attach it to your thread.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**xenith1988** · 06-26-2012, 06:09 AM

Alright its on the way...

**xenith1988** · 06-26-2012, 06:17 AM

**Uploaded...

It's a single slide from the workbook.

**AB33** · 06-26-2012, 06:18 AM

please note: This is not my code. It's Ron's one. I am new to VBA and still learning. By pure concidence, I am playing around with the find function and saw your post. It might not work, or not the code you are after, but it does give you a start. Your best bet is with Jerry.

Please Login or Register  to view this content.

**AB33** · 06-26-2012, 06:22 AM

Please Login or Register  to view this content.

**xenith1988** · 06-26-2012, 06:34 AM

This seems to be a step in the right direction I wager, just it obviously will need work. I can run the script and from what I am reading it seems it's found what it looks for, I just have to tell it what to do with what it's found. Or... am I reading it all wrong?

(Naturally I changed the values from Mr. Bobby etc to Mr. Richardson)

**xenith1988** · 06-26-2012, 06:53 AM

There's many ways to copy data; which would be the best way to incorporate it with the scheme you listed above?

**xenith1988** · 06-26-2012, 07:24 AM

Please Login or Register  to view this content.

This one seems to be closer to the real deal but... it's not the real deal. What's not working here? (Obviously it needs a lot of patching but I am way too novice. 12 hours on this so far, god I'm making nearly no progress. Or atleast it feels that way; I feel like I have been one step away from completion the last few hours)

**xenith1988** · 06-26-2012, 07:46 AM

6 hours remaining for a solutions. It looks like its a twist between the first script posted and the last one. I am not sure how to merge the two together to function over the span of the entire workbook. I'm trying to figure it out bit by bit.

-I see coding/scripting like a body of water. Normally you want to step into the ****** pool first, wade a bit, and move up to the shallow end of the pool. I feel like I jumped into the ocean.-

**xenith1988** · 06-26-2012, 09:09 AM

I see an alternative way of doing things. I am to record how many hours each instructor has performed and fortunately they have organized the excel spreadsheet in a manner that each row represents one hour. UNfortunately, lunch hours, blanks, and other wonderful things *i haven't checked yet* can not be considered an hour of worked time.

I have thought up a method... that would work something similar to this:

Select Row 1, search for string "Instructor", Select Column for found String.
Move to Row 2 Column "Instructor", (the x:y coordinate should be defined through the search)
If Row 2, Column "Instructor" has a value, value = InstructorName
Move to Row 3 Column "Instructor",
Else If value = Nothing, Paste InstructorName
End If Row = 2000

Something like that to populate the entire column with the correct Instructor names... but don't end sub, continue like,

Search each Row for string "Lunch" or "lunch" (maybe an array?),
If "lunch" or "Lunch" = True,
then select cell = (current Row, Column "Instructor")
Delete value in cell,
End if row 2000,

Now every row with Lunch has no instructor name. There will be additional things I'll need it to search for, but once the syntax is laid out before me in this format I think I will be able to plug in for it, one more thing left to do...

For (Row, Column "Instructor") If cell = has value then enter value "1" in (same row, column I)

This will give each row a 1 hour value. Each time a lunch hour is observed it should be a blank space in the I Column. That will allow me to then do a follow up with AutoSum per InstructorName. Or something like that... my hopes is to tally up each instructors allotted work hours. I have different ideas on the final approach but I am not exactly sure how to make that happen. This whole function needs to work across the workbook on every sheet. Then I'm guessing I will need to autofilter all worksheets, organize it by instructor again, take all values from column I for that instructor, sum them up, and post it in either a new sheet in the current workbook or in a new workbook as
InstructorName in one column and the TotalSum in the next column on the same row.

John Smith 152 Hours.

With that reached I'll do the manual looking up and down the whole excel document to find out how long the guy has been employed, and base that into how many hours he has worked. (Worked with the company 2 years and has done 140 hours of class instruction time etc etc) With that information, I can then, FINALLY, build a bar graph or a pie chart comparing different members of the company together based on hours worked.

The whole project of this is to compare military instructors to civilian / sub-contracted contractor instructors. The contract is due to expire soon, and the military has kept a very sloppy record of worked hours during the last 5 years for this POI. The personal in charge of the record keeping is military himself... biased views are taking place based on inaccurate hours worked. They have the feeling they can support the full instructing load with just military staff while, with the data already recorded, that is not true. However, until we can organize this data into a more compact and presentation ready format, they will not believe otherwise. That's where I am at with this project.

ANY other angles to complete the final result will be absolutely appreciated. I am running out of steam and creativity. Thanks again for all you guys's input and suggestions thus far, I look forward to the final document!

Cheers again!
-X

**AB33** · 06-26-2012, 11:15 AM

What this code supposed to do is to search through each sheet with in the active worksheets. If it found your choosen strings, it will copy the entire row in to a new sheet.As I said, I am learner my self and I am sure there are lots of experts in this forum who could improve the code

Please Login or Register  to view this content.

**xenith1988** · 06-26-2012, 11:20 AM

@AB33: I have tried your code and changed the strings to Mr Richardson and etc. It doesnt seem to work. (My sample workbook is attached to post #1) Give it a shot and see if you can figure out a method. >.< I have yet to sleep and I feel I am going loopy at this point haha

**AB33** · 06-26-2012, 11:47 AM

slightly improved one

Please Login or Register  to view this content.

**xenith1988** · 06-26-2012, 11:54 AM

Have you tested that in the workbook I provided? Again, the Bobby values should be changed to Mr. Richardson and etc. I tried it and it looks like it searches by sheet only, not the workbook. I'll tinker around with this one though and see where it lands me.
-X

I do appreciate the contribution though! It's keeping my head spinning. Which means I'm not asleep yet (hopefully) man, there has to be a way to get this running. I'm looking for a solution (which is bad) instead of learning the VPA syntax. Things are easier to read now for me. I am starting to understand whats happening under the hood, I just... can't write the next line of codes. I think of 20 different ways or writing but I can't connect the dots. It's total jumble. I need an intermediate scripter dude to roll up in here. Pretty much be like, "Here you go chump. Now scam!"

I'm losin my mind on this. I gonna dream VPA and wake up whispering scripts. lol =|

**AB33** · 06-26-2012, 12:58 PM

No, the code I provided does not search in to directory; it only search a single workbook. I think you need similar code but one which seacrches through a Dir path. If no one could help in this forum, try other forms, like the cage and Banter

Search Data from Multiple Sheets and copy found data to new workbook

LinkBack

Thread Tools

Rate This Thread

Display