Use cell data for filter criteria

[Lvenom]

I have had no luck searching for an answer so better to ask. I am using auto filter on Sheet1 and filtering a column of dates. What I need is for the criteria for the auto filter to be pulled from cell H1 on Sheet 2. Is this possible or is auto filter the wrong direction to go? I am looking to find all the rows on Sheet 1 that contain the date found in cell H1 on Sheet 2. Then I am going to cut and paste that data to another worksheet. I think I have all the rest of it figured out but am stumped at the filtering portion. Any help would be greatly appreciated.

[Palmetto]

You should be using Advanced Filter. It accepts/requires criteria in cells.

[Lvenom]

I tried the advanced filter option. It tells me I can only copy to the active page. I was looking more for a macro, that would filter the data based on a date found in cell H1 on Sheet #2. Once the data was filtered by the date, I was then going to copy the data in the visible cells in columns A, B, C and D to another spreadsheet in same workbook. I have data in columns up to M (13 columns). Thank you for the quick response though.

[Palmetto]

I tried the advanced filter option. It tells me I can only copy to the active page.

In order to use Advanced Filter and copy the data to a sheet other than the source sheet, you must start the Advanced Filter while on the sheet you want the values copied to.

See this link for a better understanding on Advanced Filter

[Lvenom]

Still not working for me. I am attaching a sample spreadsheet. What I am looking to do is filter the data on sheet "Schedule" by column J (Schedule) which is the date field. The criteria for the filter comes from cell H1 on sheet "Monday". Once the data is filtered by that date on sheet "Schedule", I want to copy the visible data from columns A, B, C and D and paste them onto sheet "Monday" at cell A10. Hopefully this is clear and helps. I am working in Excel 2003

[Palmetto]

Try this. On the Schedule sheet, cells Z1:Z2 are used as the critieria cells for the advanced filter. Z1 = header name, Z2 = link to sheet2!G1 (date cell)

Open the attached workbook and press Alt + F8 keys to open the Run Macro dialog, then choose the Date_Filter macro.

Please Login or Register  to view this content.

[Lvenom]

Thank you Palmetto, that does work as needed. I was hoping that you may have had a simpler solution so I could work out the rest on my own. I am attaching the full workbook with all data so you could see what I mean. The intent is for a user to review the data on the "Schedule" sheet, set the schedule date for what he/she wants done in the upcoming week (sheets Monday, Tuesday etc), then be able to click on a button on the toolbar that would be associated with a macro that would do exactly what you had done for just the Monday alone (i.e. copy and paste just the filtered data to the appropriate sheets). The day sheets are all formatted the same, the "Schedule" sheet changes as it is data imported from a different program. Hate to ask but do you think the macro you have written can be adapted to do what I have described above? Thank you for all the help so far.

[Palmetto]

See if the attached works for you. It work well with the limited data in the schedule, though I had to change the date on the Monday sheet to get a valid date contained in the data.

Based on your sample workbook, the Monday sheet is the "date entry" sheet and dates in all other sheets are incremented accordingly.

The code is set up to runs as follows:
When the date is changed on the Monday sheet, then the schedule is filtered and applicable entries are copied to the Monday Sheet. On all subsequent sheets, just activating the sheet will run the code to filter and copy entries. If you want manual execution of the code, then the macro could be assigned to a button on each sheet, which requires removal of the code from the worksheet modules.

Please Login or Register  to view this content.

Code for "Monday" sheet

Please Login or Register  to view this content.

Code for all other sheet module (except "Schedule")

Please Login or Register  to view this content.

[Lvenom]

Looks and works very well. Thank you very much Palmetto for your support on this. Great learning tool for me to expand on the little I already know. Again thank you very much. The only issue at the moment that I can't seem to find, when I go off and then back onto a sheet, rather than copying and pasting in row A10 it jumps down 2 rows and pastes there. This happens only when there is more than one line of data being pasted over. One line only, always pastes into the correct A10 row every time you open the sheet. For multiple data, every time you open the sheet it drops down 2 lines. Any thoughts? Take a look at the Thursday sheet to understand what I am saying.

[Lvenom]

Never mind. I was able to follow through on the example you provided and found where I needed to make the necessary changes to alter what was happening. Very nice job. Again thank you.