Generate New Tabs Based off of Data in a Column

[leocomerlato]

My knowledge of Microsoft Excel is limited. One of the things I have to do on a daily basis is a data pull. I then filter the data by the date column. I create a new tab for every date and post the corresponding data for that date in the tab.

Is there anyway to automate this?

Microsoft Excel 2010
Windows 7 SP1 64Bit

 

[sijpie]

Anything you can do manually you can automate.
Describe what the data looks like, what you filter it on (and in which columns), etc.

 

[leocomerlato]

Anything you can do manually you can automate.
Describe what the data looks like, what you filter it on (and in which columns), etc.

The below is just an example I whipped up. I'd want to be able to base it off the Received Date column. The way I name the tabs is May 8, October 3, etc.

OWNER_TEAM_NAME	RECEIVED_DATE	STATUS_CD	CCP_INQ_KEY	CONTACT_ID	INQUIRY_ID
TEAM - A	08-May-15	OP	000000000000000000000	000000000000	000000001
TEAM - A	03-Oct-14	OP	000000000000000000000	000000000000	000000001
TEAM - A	18-May-15	OP	000000000000000000000	000000000000	000000001
TEAM - A	27-Jan-15	OP	000000000000000000000	000000000000	000000002
TEAM - A	14-May-15	OP	000000000000000000000	000000000000	000000001

<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim bottomB As Integer
    bottomB = Range("B" & Rows.Count).End(xlUp).Row
    Dim c As Range
    Dim rng As Range
    Dim ws As Worksheet
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B" & bottomB), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A1:F" & bottomB)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    For Each c In Range("B2:B" & bottomB)
        Set ws = Nothing
        On Error Resume Next
        Set ws = Worksheets(Format(c, "dd-MMM-yy"))
        On Error GoTo 0
        If ws Is Nothing Then
                Worksheets.Add(After:=Sheets(Sheets.Count)).Name = Format(c, "dd-MMM-yy")
        End If
    Next c
    For Each rng In Sheets("Sheet1").Range("B2:B" & bottomB)
        For Each ws In Sheets
            If Format(rng, "dd-MMM-yy") = ws.Name Then
                rng.EntireRow.Copy Sheets(ws.Name).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            End If
        Next ws
    Next rng
    Application.ScreenUpdating = True
 End Sub

 

[leocomerlato]

You'll have to excuse me. I've never used code so I tried googling it and finding a way to do it, but I think I may be doing it incorrectly as I'm getting an error. How would you suggest I go about implementing this?

 

[mumps]

Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

 

[leocomerlato]

Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

That works! You're the best! Is there a way for it to import the First Row Headers too?

 

[mumps]

Place this line:

Sheets("Sheet1").Rows(1).Copy ActiveSheet.Cells(1, 1)

below this line:

Worksheets.Add(After:=Sheets(Sheets.Count)).Name = Format(c, "dd-MMM-yy")

 

[leocomerlato]

That worked perfectly. Thanks for taking time out of your day to help me. I really appreciate it.

 

[mumps]

My pleasure.