Macro that only filters the column with the current date

[Anonymous321]

Hi.
I'm wondering if there is a way to set up a macro button that, once clicked, only filters the column that has the current date (the table headings are the months of the year). I know how to set it up so it filters a certain month (copy and pasted below). But it would be nice that, rather than me changing the macro code every month it automatically changes every month

Sub Free_Feb()
'
' Free_Feb Macro
'

'
ActiveSheet.Range("$E$3:$L$8").AutoFilter Field:=2, Criteria1:="Free"
End Sub

This is an example of the table I plan on filtering

Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21
Free	Busy	Busy	Busy	Busy	Busy	Busy	Busy
Free	Free	Free	Free	Free	Free	Busy	Busy
Free	Busy	Busy	Busy	Busy	Busy	Busy	Busy
Free	Busy	Busy	Busy	Busy	Busy	Busy	Busy
Free	Free	Free	Free	Free	Free	Free	Free

 

[Peter_SSs]

Do you mean something like this?

Sub Free_This_Month()
  ActiveSheet.Range("$E$3:$P$8").AutoFilter Field:=Month(Date), Criteria1:="Free"
End Sub

 

[Anonymous321]

Do you mean something like this?

Sub Free_This_Month()
  ActiveSheet.Range("$E$3:$P$8").AutoFilter Field:=Month(Date), Criteria1:="Free"
End Sub

Worked a charm, thanks . Any idea what i need to replace in this code if I had a button that filtered the next month, rather than the current

 

[Anonymous321]

Worked a charm, thanks . Any idea what i need to replace in this code if I had a button that filtered the next month, rather than the current

SOLVED: I just did the same formula but added a "+ 1" after the closing bracket next to 'date' and before the comma.

 

[Peter_SSs]

SOLVED: I just did the same formula but added a "+ 1" after the closing bracket next to 'date' and before the comma.

What if the current month is December? Which column would you then want filtered for "next month"?

 

[Anonymous321]

What if the current month is December? Which column would you then want filtered for "next month"?

Good question. I would want January 2022 filtered, but obviously this wouldn't work using the +1 method. Any ideas?

 

[Peter_SSs]

I would want January 2022 filtered

Which column would that be in?

 

[Anonymous321]

I deleted that other spreadsheet as it was just an example, but this is what it would look like if I added dates after December. Jan 2022 is column E in this spreadsheet

Oct.2021​	Nov.2021​	Dec.2021​	Jan.2022​	Feb.2022​	Mar.2022​
Free	Free	Free	Free	Free	Free
Busy	Busy	Busy	Busy	Free	Free
Busy	Busy	Busy	Busy	Free	Free
Busy	Free	Free	Free	Free	Free
Busy	Busy	Busy	Busy	Free	Free

Plus: That original code wont work if the table doesn't start with January which might become a problem in later months

 

[Peter_SSs]

Does the column position change over time or will October 2021 always be in B?

 

[Anonymous321]

Does the column position change over time or will October 2021 always be in B?

October will always be B