Split one tab into many based on date criteria

[mjainduke]

Hello,

I'm new to the forum and to VBA. I have a sheet with one tab that has several columns. One of these columns is a date. I'd like to split the rows into multiple tabs based on the date criteria in the tab.

So, the first new tab should be all the rows >= April 15, 2017. The second new tab should be all the rows >= May 20, 2017. So forth and so on.

I have 25 such dates.

There will be duplication of records from tab to tab. In other words, the tab ">April 15" will have records that are also in the "> May 20" tab.

Grateful for the help.

 

[pbornemeier]

What is the name of the worksheet(tab) that has the date column?
What column (letter) contains that date?

 

[mjainduke]

Hi,

Thanks for your response. The name of the worksheet (tab) is Master. The column with the date is "e".

Thanks,
Mukund.

 

[pbornemeier]

In your workbook with the 'Master' worksheet, create a worksheet with the name 'Dates'. Put the 25 dates in A1:A25.

The code will create worksheets with names like 2017 04 15 and 2017 05 20. If you want different worksheet names, modify the quoted string in this line:

sName = Format(rngCell.Value, "yyyy mm dd")

to produce the desired date format name.

See link in my sig on how to use the code.

Option Explicit

Sub SplitMasterByDate()
    'Assumes
    '  a worksheet named master with dates in column E
    '    with data & headers starting at A1 and no blank rows
    '  a worksheet named Dates with dates in column A, starting with A1
    '    and down with no blanks.  These dates will determine
    '    data worksheet names and the rows moved to those worksheets
    '  No other worksheets (with names that conflict with 'Dates' worksheet's) dates
    'Worksheets will be created with a name like yyyy mm dd for each of the dates
    '  on the 'Dates' worksheet
    
    Dim lDatesLastRow As Long
    Dim sName As String
    Dim rngCell As Range
    
    Worksheets("Master").AutoFilterMode = False
    For Each rngCell In Worksheets("dates").Range("A1").CurrentRegion.Cells
        sName = Format(rngCell.Value, "yyyy mm dd")
        Worksheets.Add(after:=Sheets(Sheets.Count)).Name = sName
        Worksheets("Master").Range("A1").CurrentRegion.AutoFilter _
            Field:=5, Criteria1:=">=" & DateSerial(Year(rngCell), Month(rngCell), Day(rngCell)), Operator:=xlAnd
        Worksheets("Master").Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
            Destination:=Worksheets(sName).Range("A1")
    Next
     Worksheets("Master").AutoFilterMode = False
     
End Sub

 

[mjainduke]

Hello. I really appreciate the help.

This worked in creating new tabs - one for each of the dates in the "Dates" tab, but it didn't populate the tabs with their respective filtered data. Am I doing something wrong?

Thanks!

 

[pbornemeier]

It worked for my test data so I believe the dates in column E on Master are text strings, not "real" dates.
If they are strings, format that column as short date and add 0 to each value in the column by copying an empty cell, selecting column's data and using PasteSpecial Add.
The code assumes that the data/headers starts in A1 and has no blank columns or rows, if that is not the case, please explain.
Let me know how it works after the above.