Hello everybody,

I have many data in sheet2 there are many dates(f2:f30000)

how to make reports by month (f2:f30000) from sheet2 to new sheets?

Please can you help me to get a perfect code for this purpose? I am very new to this macro coding and need support from you.

Thanks You

• Edited by Amek.inf Friday, May 08, 2015 3:11 AM

I have One workbook but i have six sheets in it,

1. list product database

2. in product database

3. out product database

4. report in product databse

5. report out product database

6. Menu 

i can resolve when i input data to in_product_database, it can link into report_in_database, but i can't filter data in report_in_database by month....

this is my file :

https://onedrive.live.com/redir?resid=81A4FF7ECD34BA8E!107&authkey=!AMDMP_uLHlOh9jA&ithint=file%2czip

Thank you

I am having difficulty relating your description to the actual example workbook. It would have been better if you used the actual worksheet names in the description.

However, the following information might help with your question " but i can't filter data in report_in_database by month...."

To manually filter by month (To help explain what the code below is doing)

1. Click the filter DropDown.
2. Select Date filters (above the Search field).
3. Select Between.
4. At the far right of the first field select the date picker and select first day of required month.
5. At the far right of the second field select the date picker and select last day of required month.
6. Click OK.

If you record the macro code to do this you will get something like the following:

ActiveSheet.ListObjects("Table26").Range.AutoFilter Field:=6, Criteria1:= _
        ">=1/05/2015", Operator:=xlAnd, Criteria2:="<=31/05/2015"

However, the recorded code will not work when the date is recorded in d/m/y/ format so it must be changed to a serial date that is concatenated with the ">=" like the following code example that calls a UDF to convert a text date to SerialDate.

Sub SetMonthFilter()

    Dim lngStart As Long    'For Start date as DateSerial
    Dim lngEnd As Long      'For End date as DateSerial
   
    lngStart = SerDate("1/05/2015")  'Start date as DateSerial
    lngEnd = SerDate("31/05/2015")   'End date as DateSerial
   
    ActiveSheet.ListObjects("Table26").Range.AutoFilter Field:=6, Criteria1:= _
        ">=" & lngStart, Operator:=xlAnd, Criteria2:="<=" & lngEnd

End Sub

Function SerDate(strDate As String) As Long
    Dim arrSplit As Variant

    arrSplit = Split(strDate, "/")
    SerDate = DateSerial(arrSplit(2), arrSplit(1), arrSplit(0))
   
End Function

Just for interest, if you edit the dates in the recorded code and change to m/d/y format like the following then it also works.

ActiveSheet.ListObjects("Table26").Range.AutoFilter Field:=6, Criteria1:= _
        ">=5/1/2015", Operator:=xlAnd, Criteria2:="<=5/31/2015"

The following also works for me but I understand that using the alpha abbreviation for the month does not work in all localities so you would need to test your locality to see if it works.

ActiveSheet.ListObjects("Table26").Range.AutoFilter Field:=6, Criteria1:= _
        ">=1-May-2015", Operator:=xlAnd, Criteria2:="<=31-May-2015"

Ok.

Thank You