Filter months & generate new tabs from results

[matrixpom]

Hi all!

I have an awesome snippet of code which looks at a column of months (mmm) & generates a new tab for each month then copies all rows using that month respectively. May be easier if you view my workbook attached. The problem I am having is the month colunm I will eventually have will be generated from column "B" which will have a series of full dates & the only way I can generate months as "mmm" is by using...

=IF(B2,B2,"")

...in column "G", sadly when I use a formula to achieve this my VBA doesn't work? Is there another way to make this work? Thanks for any help guys, as always much apreciated - Marc

[Sintek]

Sub PopulateCol_G()
With Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
    .Formula = "=IF(B2="""","""",TEXT(B2,""mmm""))"
    .Value = .Value
End With
End Sub

=IF(B2="","",TEXT(B2,"mmm"))

For interest sake...Another version of your code...

Sub sintekJ3v16()
Dim Data, Dict As Object, i As Long
Set Dict = CreateObject("Scripting.Dictionary")
With Sheet1.Cells(1).CurrentRegion
    Data = .Value
    For i = 2 To UBound(Data, 1)
        If Not Dict.Exists(Data(i, 6)) Then
            Dict.Add Data(i, 6), 1
            If Evaluate("ISREF('" & Data(i, 6) & "'!A1)") = False Then
                Sheets.Add(, Sheets(Sheets.Count)).Name = Data(i, 6)
            Else
                Sheets(Data(i, 6)).UsedRange.Delete
            End If
        End If
        .AutoFilter 6, Data(i, 6)
        Union(.Columns("A"), .Columns("D"), .Columns("F")).Copy Sheets(Data(i, 6)).Range("A1")
        .AutoFilter
    Next i
End With
End Sub

[matrixpom]

Hi Sintek,
My main sheet will have the code

=IF(B2="","",TEXT(B2,"mmm"))

populated down column "G" for 10,000 rows, sadly when the VBA code filters, it always filters a blank page for the month code...would there be a way of doing without the month code alltogether & generating a month tab from the current dates in column "B"? Thanks again Sintek - Marc

[Sintek]

All works with your sample file...What is different?

[matrixpom]

Hi Sintek,

Sorry my mistake, works perfectly!! You're a legend! Marc

[Sintek]

Glad you got it sorted...tx for rep +