Filtering dropdown box to untick current month, then tick following months

Question

Good day all. I'm fairly new to VBA and I have certain questions at hand. I'm currently automating a report in my line of work, and most of it has to do with filtering data to show current month, next month onward, and the next 2 months onward.

My questions are:

1. How do you filter a drop down box for it to un-tick the previous months, tick the next month (with respect to the current month), and to tick the following months ahead (without having to hard code that it would have to end at a certain month. Just coding it to available months ahead)? For example, my data is for November 2017. I would have to un-tick the previous months, including November, then tick December 2017 and the following months ahead

   From recorded macro:

November as current month (manually filtered)

   ActiveSheet.Range("$A$1:$AY$18103").AutoFilter Field:=37, Operator:= _
        xlFilterValues, Criteria2:=Array(0, "10/18/2018", 1, "12/28/2017")

The current month code that I use in a different process. Can I incorporate this also?

 ActiveSheet.Range("$A$1:$AY$18103").AutoFilter Field:=36, Operator:=xlAnd, _
    Criteria1:=">" & Application.EoMonth(Now, -1), _
    Criteria2:="<=" & Application.EoMonth(Now, 0)

2. Similar to no.1, un-tick the previous months, tick the 2nd month from the current month, and to tick the following months ahead? (e.g. My data is for November 2017. I would have to un-tick the previous months, including November, then tick January 2018 and the following months ahead)

3. One of the processes that I have to do is to copy the formula from the cell to the left, then paste it to the current cell. What I do is I copy the left cell first, then use Ctrl+Down to go to the bottom row. Then click the right button (to go back to the original column) then Ctrl+Shift+Up again to paste to the whole column. The code that I use is:

   Range(Selection, Selection.End(xlUp)).Select
   

The problem is that there are certain cells in the original column that's already filled up with data (from my previous process) and I would have to at least write that code 4 times just for the cell to reach the uppermost cell. How do I code it so that it would reach the top (minus the row with the cell header) while taking note that these rows are filtered?

Answer 1

You got quite a lot of questions in a single post. But anyways, for your 1 & 2, you can try something like below:

Sub ject()

    Dim lr As Long
    Dim myRange As Range, selMonth As String

    '*/ Below selects 1 month ahead of current month
    'to select 2 months ahead, add 2 on Month(Date) */
    selMonth = Format(DateSerial(Year(Date), Month(Date) + 1, 1), _
                      "m/d/yyyy")

    With Sheet1 '/* change to your actual sheet */
        .AutoFilterMode = False '/* reset all filters */
        '/* reference last row to column A */
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        Set myRange = .Range("A1:AY" & lr)
        myRange.AutoFilter 36, ">=" & selMonth
    End With

End Sub

For number 3, you can use the same routine as above to find the last row (and column) and then paste data there. You can also check this out to learn more about finding your destination ranges (cells).