Macro to search and copy rows based on date/time and Team.

[Darkflame808]

Hi, I have a tough one for you guys. I have a spreadsheet that has data submissions from field agents. These submissions are made using a web front end and basically have a large amount of submissions from various dates and teams that i'd like to parse using a macro.

Basically the macro works like this.

It reads the start and end dates of two fields. It searches Column A for any match that falls within that criteria then subjects that row to further analysis.

If Column C matches any of the search terms I have requested, the entire row gets copied from Sheet1 to Sheet3

A text example for those who do not wish to view the sheet would be like this.

Sheet1 contains raw data
...............A.............................B............................
1.......11/1/11 14:30.............1PM
2.......11/1/11 08:21.............2PM
3.......11/2/11 04:00.............3AM
4.......11/2/11 08:00.............1AM

On Sheet2
I'd like to have a Start<->End date criteria. It's only a date search for please note my A column is date / time but 11/1/1 would mean 11/1/11 00:00 -> 11/1/11 23:59 as an example.

I have another set of fields that say 1AM, 2AM, 3AM, 4AM, 1PM and so forth. These are switches based on 0 or 1.

If the Start Date was 11/1/11 End Date was 11/2/11
And the switches were 1AM->5AM all on, 1PM-5PM all on then every row in that table above would be copied from Sheet1 to Sheet3

If Start Date was 11/1/11 End Date was 11/2/11
And the switches were 1AM on, 1PM on, rest off, then rows 1 and 3 would be copied from Sheet1 to Sheet3.

I hope this best describes my goal.

Thank you very much, look forward to seeing how this can be worked out.

If searching for a result within a result is too much to ask, a simple copy all rows based on date would be fine and I can sort and pick out each section from there.

[Darkflame808]

*sigh* I fail.....I posted the spread sheet of a question I had before in a post containing questions I have now.

I'm sorry guys.

My goal was to examine sheet 1 and do basically this.

Copy rows of data that fall between two dates AND teams based on the switches described in the darksexample.xls.
I've spent the last two days trying to simplify my goal because it looks like i'm trying to do too much in one macro sweep.

Here's a way I can accomplish my goal using the same type of macro but slightly modified.

Here's the run down....

1. A macro that takes searches Sheet1!A:A for dates that fall between Sheet2!E7 and Sheet2!G7 and copies those results to a tempdata sheet.

2. Now do a if statement stating that IF Sheet2!B7 = 1 then, rerun the above macro except the new terms would be search tempdata!C:C for 1AM and copy all rows to tempdata2. I would like only the values pasted.

3. Now do a if statement stating that IF Sheet2!B8 = 1 then, rerun the macro in #2 but changing search terms for tempdata!C:C to be 2AM, copy all rows to tempdata2 again, only the values pasted.

4. Rinse and repeat using tempdata2 to hold the end result of all searches. Then put all results of tempdata2 into Sheet3.

This will run the same macro 10 times pretty much.

The first run pulls all files meeting that date. puts all rows that fall between those dates in tempdata.

Now we run 10 macro's on tempdata each saying if sheet2!b7=1 copy all rows with sheet2!a7 appearing in sheet1!c:c to tempdata2:a2 onward pasting only the values. rinse and repeat this macro changing sheet2!b7 and sheet2!a7 with sheet2!b8 and sheet2!a8.

In the end tempdata2 holds everything that was regurgitated from the 10 macro runs.

I don't care which order data appears as I have other scripts that sort it all out into what I need.

I sincerely apologize for the confusion my post and spreadsheet made. I thought after opening my attachment 2 days something got corrupted but I simply posted the wrong spreadsheet.

Again thank you!

[shaukat]

dude, A macro is needed to understand your statements

[Darkflame808]

dude, A macro is needed to understand your statements

Lol I think I can figure out how to get it done by butchering up some other macros.

The examples in the spreadsheet clearly show the goal I'm trying to achieve.

[arlu1201]

Is this solved?

[Darkflame808]

Not solved but i'll figure it out on my own.