PDA

View Full Version : Filter/paste rows by months in the near future



elysa
10-05-2020, 07:24 AM
Hi there,

I am new to VBA and I am struggling to define "filters" by which some of my data should be copied to an overview sheet. I would like for all rows to appear that contain in one specific column the current month plus all dates in the next two months (so all that contain October, November and December for example). My current date format is MMMM YYYY (Oct 20 etc.). The code I use works very well, but I dont want to update it each month. I tried a few codes I found online but nothing worked, maybe because I dont use Autofilter or because my date format is a bit weird? I just want the condition on which it filters to be >= Month (Now) + 2, but of course in such a way that Excel reads it.

Here is the full code I use:


Public Sub CopyRows()

Dim ws As Worksheet
Dim s_Main As String
Dim nRow As Long
Dim Last_row As Long
Dim i As Long
Dim Table As Variant


s_Main = "Overview"
Last_row = Worksheets(s_Main).Cells(Rows.Count, 1).End(xlUp).Row
Worksheets(s_Main).Range("A2:P" & Last_row).ClearContents


For Each ws In Worksheets
If ws.Name = s_Main Then
GoTo Change_ws
Else
nRows = ws.Cells(Rows.Count, 1).End(xlUp).Row
ReDim Table(nRows, 16)
Table = ws.Range("A1:P" & nRows)
For i = 1 To nRows
If Table(i, 2) = "01.09.2020" Or Table(i, 2) = "01.10.2020" Or Table(i, 2) = "01.11.2020" Or Table(i, 2) = "01.12.2020" Then
Last_row = Worksheets(s_Main).Cells(Rows.Count, 1).End(xlUp).Row
ws.Range("A" & i & ":P" & i).Copy Worksheets(s_Main).Range("A" & Last_row)(2)
End If
Next i
End If
Change_ws:


Next ws

End Sub
Any help is appreciated, thank you! :)