[Solved] Macro to enable auto refresh of advanced filter
[Solved] Macro to enable auto refresh of advanced filter
Hi all,
First time posting here so apologies if i haven't followed any rules/etiquette!
Essentially what i am trying to do is hide rows based on a given cells value (todays date), i am able to do this manually by selecting the range (A25:F61) then advanced filter (A21:F22). However what i would like is that the date changes automatically using today() then the filter reapply's itself so that only today's information is displayed. I attempted to record a macro of this but it wouldn't work, i have no grasp of macros, i have even been looking at other examples online and attempting to modify them for my sheet but to no avail, would someone be kind enough to write one for me?
Kind Regards
Marco
First time posting here so apologies if i haven't followed any rules/etiquette!
Essentially what i am trying to do is hide rows based on a given cells value (todays date), i am able to do this manually by selecting the range (A25:F61) then advanced filter (A21:F22). However what i would like is that the date changes automatically using today() then the filter reapply's itself so that only today's information is displayed. I attempted to record a macro of this but it wouldn't work, i have no grasp of macros, i have even been looking at other examples online and attempting to modify them for my sheet but to no avail, would someone be kind enough to write one for me?
Kind Regards
Marco
- Attachments
-
- Untitled 2.ods
- (12.33 KiB) Downloaded 153 times
Last edited by Hagar Delest on Mon Dec 10, 2018 9:06 am, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
Open Office 4.1.5 - Windows 10
Re: Macro to enable auto refresh of advanced filter
Look at this: download/file.php?id=136
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Macro to enable auto refresh of advanced filter
Code: Select all
ThisComponent.DatabaseRanges.getByName("your_db_range_name").refresh()
menu:Data>Refresh does the trick without macro.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Macro to enable auto refresh of advanced filter
Hi Villeroy,
I am looking at the first post you sent, would prefer it to be entirely automated as i want to make it as fool proof as possible (End users will vary in levels of competency) I have followed the instructions step by step but this message appears upon entering anything in the text box;
I'm not sure what it means or what i have done wrong.
Thank you for your help so far
I am looking at the first post you sent, would prefer it to be entirely automated as i want to make it as fool proof as possible (End users will vary in levels of competency) I have followed the instructions step by step but this message appears upon entering anything in the text box;
I'm not sure what it means or what i have done wrong.
Thank you for your help so far
- Attachments
-
- Untitled 2.ods
- (13.48 KiB) Downloaded 160 times
Open Office 4.1.5 - Windows 10
Re: Macro to enable auto refresh of advanced filter
There is no database range named "Import1". What is the name of your database range?
menu:Data>Define...
Define a database range, delete all macro bullshit and call menu:Data>Refresh once a day to update your date filter.
menu:Data>Define...
Define a database range, delete all macro bullshit and call menu:Data>Refresh once a day to update your date filter.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Macro to enable auto refresh of advanced filter
Hey,
Defined the range, labelled it as data, now the following message is appearing
I appreciate what you are saying about updating once daily manually, but that is not what i want, it has to be automated, please help
Defined the range, labelled it as data, now the following message is appearing
I appreciate what you are saying about updating once daily manually, but that is not what i want, it has to be automated, please help
Open Office 4.1.5 - Windows 10
Re: Macro to enable auto refresh of advanced filter
There is no named range named "criteria".
Delete all macro bullshit and call menu:Data>Refresh once a day to update your date filter.
Delete all macro bullshit and call menu:Data>Refresh once a day to update your date filter.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Macro to enable auto refresh of advanced filter
There is a range named criteria,
Your proposed second solution doesn't meet my criteria, why should i have to settle if what i would like is possible. I really, really appreciate your help as i cannot do this on my own but please meet the conditions!
Your proposed second solution doesn't meet my criteria, why should i have to settle if what i would like is possible. I really, really appreciate your help as i cannot do this on my own but please meet the conditions!
Open Office 4.1.5 - Windows 10
Re: Macro to enable auto refresh of advanced filter
Do not mimic things you do not understand. If you want to filter by today's date, put =TODAY() in the criteria cell (no regular expression required), call Data>Filter>Advanced, select the criteria range. When the next day has come, click any cell in the database range and call Data>Refresh
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Macro to enable auto refresh of advanced filter
Thank you for the manual solution, i tried it and it does work well.
I realize what i was doing wrong, and named the criteria range, now this is the error;
Please don't give up on me!
I realize what i was doing wrong, and named the criteria range, now this is the error;
Please don't give up on me!
Open Office 4.1.5 - Windows 10
Re: Macro to enable auto refresh of advanced filter
P.S. If your dates happen to be strings (don't know why Excel users do that), the formula for the criterion would be =TEXT(TODAY();"DD/MM/YY"))
You have zero programming skills. Stop using macros.I realize what i was doing wrong, and named the criteria range, now this is the error;
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Macro to enable auto refresh of advanced filter
You are correct, i have zero programming skills.
I do however need this to be automatic not manual via refreshing the data so what else should i do?
I am here politely asking for someone to walk me through this, if that is not you then that's fine, thank you for your time.
I do however need this to be automatic not manual via refreshing the data so what else should i do?
I am here politely asking for someone to walk me through this, if that is not you then that's fine, thank you for your time.
Open Office 4.1.5 - Windows 10