split data across multiple sheets based on month

hello
I search way to split data based on month across multiple sheets .
the split data begins from row5 in COL A,B,C
the result should begins col F in multiple columns based on month withe the borders and formatting as in my result each month should be individual.
with considering I create a new sheets and the data increases ,sometimes changes the month in col B . it should update data for the new month and delete from old month for instance when run macro in first time and split based on month and return changing the month in COL B should add changing to a new month and delete from old month I put some result in some sheet to understand my Idea . so I would the macro is dynamic

I don't think to need pivot table because the data depends on the all sheets then should do by vba

vba

Sub test()
    Dim myMonths, myMin As Long, myMax As Long
    Dim i As Long, r As Range, rng As Range, temp
    myMonths = Array("Jan", "Feb", "Mar", "Apr", "May", _
        "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    myMin = Month(Application.Min(Columns("b")))
    myMax = Month(Application.Max(Columns("b")))
    Application.ScreenUpdating = False
    Set rng = [e1:e2]: temp = rng.Value
    Columns("f").Resize(, 60).EntireColumn.Clear
    For i = myMin To myMax
        rng(2).Formula = "=month(b6)=" & i
        Set r = [a5].Offset(, i * 5).Cells(1)
        [a5].CurrentRegion.AdvancedFilter 2, rng, r
        With r.CurrentRegion
            .Offset(1).Resize(.Rows.Count - 1).Columns(1).Value = _
            Evaluate("row(1:" & .Rows.Count - 1 & ")")
        End With
        With r.Cells(0, 2)
            .Value = UCase$(myMonths(i - 1))
            .Interior.Color = vbRed: .Font.Bold = True
            .HorizontalAlignment = xlCenter
        End With
    Next
    rng.Value = temp
    Application.ScreenUpdating = True
End Sub

@jindon this is awesome! but I would that for all sheets not only the first

Change to

Sub test()
    Dim myMonths, myMin As Long, myMax As Long, ws As Worksheet
    Dim i As Long, r As Range, rng As Range, temp
    myMonths = Array("Jan", "Feb", "Mar", "Apr", "May", _
        "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        myMin = Month(Application.Min(ws.Columns("b")))
        myMax = Month(Application.Max(ws.Columns("b")))
        Set rng = ws.[e1:e2]: temp = rng.Value
        ws.Columns("f").Resize(, 60).EntireColumn.Clear
        For i = myMin To myMax
            rng(2).Formula = "=month(b6)=" & i
            Set r = ws.[a5].Offset(, i * 5).Cells(1)
            [a5].CurrentRegion.AdvancedFilter 2, rng, r
            With r.CurrentRegion
                .Offset(1).Resize(.Rows.Count - 1).Columns(1).Value = _
                Evaluate("row(1:" & .Rows.Count - 1 & ")")
            End With
            With r.Cells(0, 2)
                .Value = UCase$(myMonths(i - 1))
                .Interior.Color = vbRed: .Font.Bold = True
                .HorizontalAlignment = xlCenter
            End With
        Next
        rng.Value = temp
    Next
    Application.ScreenUpdating = True
End Sub

@jindon great! thanks so much for your solution

@jindon I no know if you can help me to create hyperlink and open my files names . all the items are existed under column(NAME) should be hyperlink
my directory is "C:\Users\leopar\Desktop\report" if you can do that should be in post#6
thanks again

Change

            With r.CurrentRegion
                .Offset(1).Resize(.Rows.Count - 1).Columns(1).Value = _
                Evaluate("row(1:" & .Rows.Count - 1 & ")")
            End With

to

            With r.CurrentRegion
                Dim c As Range
                For Each c In .Columns("c").Offset(1).Resize(.Rows.Count - 1).Cells
                    c.Parent.Hyperlinks.Add c, "C:\Users\leopar\Desktop\report\" & c.Value
                Next
                .Offset(1).Resize(.Rows.Count - 1).Columns(1).Value = _
                Evaluate("row(1:" & .Rows.Count - 1 & ")")
            End With

thanks . but it gives error in this line

HTML Code: 

application defined or object defined error
note: in my directory has many subfolders and when hyperlink the files also should open when press any file
thanks for your assistance

Originally Posted by Alaa-A

note: in my directory has many subfolders and when hyperlink the files also should open when press any file
thanks for your assistance

As I said earlier, you need to to FULL path including file name and extension.
e.g
C:\Users\leopar\Desktop\report\mysubfoldername\xxx.xlsx while xxx.xlsx should be in each cell in Name column.

Chang

            With r.CurrentRegion
                Dim c As Range
                For Each c In .Columns("c").Offset(1).Resize(.Rows.Count - 1).Cells
                    c.Parent.Hyperlinks.Add c, "C:\Users\leopar\Desktop\report\" & c.Value
                Next
                .Offset(1).Resize(.Rows.Count - 1).Columns(1).Value = _
                Evaluate("row(1:" & .Rows.Count - 1 & ")")
            End With

to

            With r.CurrentRegion
                Dim c As Range
                If .Rows.Count > 1 Then
                    For Each c In .Columns("c").Offset(1).Resize(.Rows.Count - 1).Cells
                        c.Parent.Hyperlinks.Add c, "C:\Users\leopar\Desktop\report\" & c.Value
                    Next
                    .Offset(1).Resize(.Rows.Count - 1).Columns(1).Value = _
                    Evaluate("row(1:" & .Rows.Count - 1 & ")")
                End If
            End With

If you don't know the correct folder path for each file then you can not add the hyperlink.

thanks . I check the path and I use right click the folder and copy the path and paste to your code to overcome mistake actually the error is gone , but I can't open any file has hyperlink

ahh , sorry I don't focus good
even that there is a problem when add the updating for the code . it just apply for active sheet when split data in first sheet it split the same data and copy to the second sheet
may be this is not clear please see first sheet and the second you will understand what I mean.

I don't understand what you are trying to say.

What 's the problem?

the code in post#6 it split the data in col C based on month in COL B each sheet individually .
the pics1 is ok but the pics 2 is wrong I highlighted by mark blue as you see in COL H ,M not matched as in COL C and it doesn't show in month MAR .
if you note the wrong data are taken from the first sheet . I tested the code when active sheet1 . this is what happens as in pictures and also when activesheet2 . the data will be wrong in sheet1 as what happens in sheet1 by taken data from sheet2 to sheet1 .
it should split based what is in existed in each sheet individually.

It IS picking 3 rows for MAR.

I clear the data and split again . the problem still continues as in the pictures. where I mistook?

OOps, I don't know why it dropped off when I posted the code in #6.
So, we were talking about different codes...

            [a5].CurrentRegion.AdvancedFilter 2, rng, r
            With r.CurrentRegion

Should be

            ws.[a5].CurrentRegion.AdvancedFilter 2, rng, r
            With r.CurrentRegion

fantastic !! the code works excellently
million thanks for your a huge assistance !