Macro that searches and copies multiple results

Hello All,

I need a macro that will enable a user to type in a date, have the system recognize the day of the week that date falls on, and brings up results that correspond on that day of the week.

I have a list of reports that run on specific days of the week and it would be convenient the bring up results based on which date I enter.

I'm not sure if this is a filtering macro or a search and copy macro. I'm really stumped here.

Thanks,

T

Could you please provide a sample of your workbook? Thanks!

here ya go.

Hello there,

Attached is your original workbook, updated to include a macro which provides the user with a inputbox to enter the date. When the date is entered the macro copies the information from sheet1 to sheet2 and filters the data by the day of the week the date falls on.

To run the code press Alt+F8 and select the FilterMacro and select Run.

To view the code press Alt+F8 and then select Step Into. Anything that appears in green are comments that are there to help you understand the code.

Right now this macro filters into a new worksheet. If you want to filter in place so that the information can be updated delete the following portion below from the code:

Sheets("Sheet1").Select
        ActiveSheet.Cells.Select
        Selection.Copy
            
            'select sheet2 and paste the information here
            
            Sheets("Sheet2").Select
            Cells.Select
            Selection.PasteSpecial
            Application.CutCopyMode = False

and update the below code

LR = Sheets("Sheet2").Range("A6555").End(xlUp).Row

to look like this

LR = Sheets("Sheet1").Range("A6555").End(xlUp).Row

Let me know how this works for you!

Thanks!

Do you want the input box or for it to fliter by that date entered in cell one of the Cover worksheet?

That would be great thanks!

I'm sorry which one would be great?

Filter by the value entered into the cell

or

provide a inputbox to enter the date to filter by?

I'm sorry about that, I misread what you wrote.

Could you provide an input box to enter the date?

Thank you.

Here you go.
The Search by date button is now assigned the FilterMacro macro.

Thank you again.

I greatly appreciate it!!

No problem! Don't forget to mark this solved and if you don't mind give me a little star tap if I've helped Thanks!

Hello again,

Thanks again for the filter you provided.

However, I have a new problem. I now require the filter to recognize the days of the year because
there are certain entries that are active on certain days/business days of each month.

How would I go about expanding the filter so that it recognizes the days of the week AND
each calendar day?

Could you upload another sample of your worksheet so that I can see how you are using the calendar year and where it is located? The one you provided me before does not have years.


Thanks!

I understand what you did with your worksheet I'm just not sure how they apply to your filter as the year is no where to be found on your Reports worksheet and therefore there is no way of knowing which records are in which year. If you could explain a little more what you are trying to do I can attempt to help.

Thanks!

Each report is suppose to run in 2012 on a daily, weekly, monthly, and quarterly basis. I tried to simplify when the monthly and quarterly reports were supposed to run by recording the day of the month that each report is supposed to run.

The monthly reports run on either a specific calendar day or business day of each month which is labeled in the "Frequency" column on the "Reports" sheet. (ex. 3rd D = third day of the month / 1st B = first business day of the month.) So a "1" under the January column means that the reports will run on the 1st of January.

As of right now I don't need to filter in the year 2013, I just had some extra time and determined which business days corresponded in that year.

I hope this helps!

-T

Okay, I'm so sorry still not understanding.

How are your reports pulled in? Are you importing for somewhere else?

As your worksheet is set up right now the Reports worksheet does not contain a colum for the year so I'm not sure how to filter it by year. The records have no indication of the year so there is currently no way to filter it by year. I understand the Calendar tab but I'm not sure what it has to do with the Reports worksheet.

Sorry if I'm just not understand correctly but I guess my question is how do you know what year the records are in?

Sorry about this, it's hard to explain what I am looking for.

The records are in this current year. I'm not so much looking for a filter of the years but rather a filter of the months, I'm sorry that I didn't clear that up earlier.

An example would be that if someone was to enter a date which fell on a Wednesday on the first of the month, I would need a filter that would bring up every record that fell on a Wednesday as well as records that fell on the first of the month.

The Calendar seems useless at this point, but it was used to help me get a better idea of which business days each report was supposed to run.

Okay, I think I got it. If not your out of luck, just kidding we'll keep working on it

Attached is the last workbook you added. It is updated to include a second macro that runs after the first. This macro uses a private function to find out the number of the week (1,2,3 or 4) that the date falls on. So the first macro filters the worksheet by the day of the week the date falls on (M (column W), T (column X), W (column Y), Th (column Z), F (column AA), Sat (column AB), Sun (column V)) and then the second macro filters the worksheet by the number of the week it falls on.

I currently used column AC aka 29 for this but I don't think this is where you will store the week of the month it falls on. So when you decided where you want to put that information then you can change the 29 in the code to the number associated with the correct column. To test it out type in 06/13/2012 for the search and see it work.

Let me know if this is close to what you were thinking.

Thanks!

I ran the filter under 06/13/12 and if only came back with a handful of records. It filtered by the day of the week (Wednesday) but it also filtered column AC by "2nd B." It could be because I didn't reassign the column number in the code but I'm not exact sure which column I should set the filter to. Also, I tried typing in other dates and no records were brought back.

Thank you for being persistent and helpful this whole time, I'm sure we'll get it soon!

I ran the filter under 06/13/12 and if only came back with a handful of records. It filtered by the day of the week (Wednesday) but it also filtered column AC by "2nd B." It could be because I didn't reassign the column number in the code but I'm not exact sure which column I should set the filter to. Also, I tried typing in other dates and no records were brought back.

Thank you for being persistent and helpful this whole time, I'm sure we'll get it soon!

The purpose of the monthly columns was to provide each date that the monthly and quarterly reports are supposed to run. I don't know it it's possible to correspond each date with its weekday and then run the filter off of that. I don't even know if those column are helpful at all, I just thought it would be easier to display.

Is it possible to set the filter to return the "Month/Quarter Day" column based on the #B or #D of each month they are supposed to run separately from the filter of the day of thee week columns? I think what's happening is that no reports are being returned at all because the calendar filter is cancelling out the weekday filter. I could be wrong. I just need the filter to return all types of "RunCode" in column D.

I was trying to see if it was possible to filter using only the days of the week but you have make it clear that it is impossible. I apologize for not being clear about what I want, I kinda just spewed information at you left and right which made it even more confusing. I'm sorry. I will add a dates column to the worksheet. However, all of the reports run on multiple dates, do you have a recommendation of how I should set of the columns for the multiple dates?

I will also clear up the blank cell problems.

Thanks.

I added the dates the the monthly and quarterly reports. Let me know if there is anything I can do to make it easier for you, or to make it work better.

Thanks!

How do you pull information into the worksheet? Or maybe better put where does the information on the worksheet come from?

All the information comes from Oracle reporting products. The workbook is a work in progress and still has some information that needs to be filled in over time. But based on the Month/Quarter Day column, I figured out the dates that correspond to each report by using a calendar. Because the monthly and quarterly reports run once a month (or three), their dates were easy to record. But the daily and weekly reports run numerous times and recording the dates would be difficult but possible.

I just thought it would be easier to have a macro that filtered the daily and weekly reports based on their day of the week, then have the macro filter the monthly and quarterly reports based on the date.

So when the report is run what are the columns that are on it before you add any columns?

I know I've asked for a lot but is there any way you could create a new filter macro that does the exact same as the original, but instead of bringing up the corresponding day of the week, it brings up all entries with the same date that the user inputs? I added the dates to the monthly and quarterly reports and attached the workbook above.

If you can do this, I'll mark this post as solved and it can be done with.

Thanks for everything

-T

Okay per your request I updated your workbook with macro called
Sub FilterByDateFinal(). This macro is set to run when you press the filter reports by date button. What it does is ask the user to input a date. The macro then finds the month of the date (three letters i.e. Jan) and finds the column whose cell in row 1 has that in it. Then it filters this column by the date the user entered in the inputbox.

Thanks!

Thanks for everything!