Calc macro to refresh an advanced filter, or formula to workaround? [closed]

edit

First time posting a question here—hello! :)

In a nutshell: How can I make a one button macro in Calc to "reapply advanced filter (including copying the results to the defined output range)" so that a non-technical user can easily refresh the needed report? (It doesn't have to be a macro but it seems that is the only plausible solution.)

System details: I'm running LibreOffice 3.4.4 on Mac OS 10.7.4.

Now I'll give the additional details which are usually left out of questions like this, resulting in a zillion back-and-forth exchanges on why all the proposed solutions won't work. :) The basic question is already listed up above.

The situation is too complex for an auto filter or a standard filter and not complex enough to warrant creating an actual database. All that I really need are subsets of the spreadsheet data—I don't need it summarized in any way (or at least not in any way that I can't easily do with Calc). I'm pretty adept with Calc but I've never touched macros at all.

I set up a "user input" section upon which the report is based, complete with drop down lists, and set up the advanced filter criteria based on the user input. The advanced filter works beautifully and I've set it to "copy results to" a pre-defined output sheet. Only trick is, since the filter criteria themselves are based on user input (but cannot be done with just an auto filter), the output sheet from the advanced filter needs to be refreshed (i.e. generated newly based on the changed criteria) after the user makes any change to the drop down user-input section.

It seems likely to me that a macro could be set up to use my already defined "criteria range" and "data range" and "output range" and just apply the advanced filter newly. Maybe this could be tied to a button right next to the "user input" section; whatever. The thing is, I have no idea how to write a macro like that and hunting the web (with a zillion tabs open by now) has turned up absolutely nothing matching that requirement. I'm really hoping that someone here will know how to do that....

Thanks for reading and I hope you can help!

UPDATE: Thank you John Sun! Your non-macro approach is great.

One more issue with it, though—I can't work out any way to sort the output with this approach. Generally I'll want to sort it by student, then by subject, then by date. Sometimes I would use slightly different sort orders, but because all the data cells in the report are dynamic, I can't sort them at all without manually copying first and "pasting special" without formulas. This is why I initially gave up on the formula approach. By contrast, advanced filters give a static output when used with "copy results to...", and so can ... (mehr)