Creating New Tabs from Values in Raw Data with Macro

[qvedder]

Hello everyone,

I am having a problem coming up with the correct VBA code for a macro that I am developing, and I was hoping someone could provide some assistance.

I am attempting to develop code that will create new tabs within my workbook based on values within a raw data set, label the tabs based on the data value, then perform functions such as creating charts and graphs for those data values. Here is a small example of the type of data I am working with;

Department EmplID Rating
A 1 1
A 2 4
B 3 2
C 4 1
C 5 6
C 6 1
D 7 3
E 8 2
E 9 2
E 10 7
F 11 5

What I would like the code to do is create new tabs for each new department (A through F), label the tabs with that value (A through F), then execute formulas, charts, and graphs. I already have all of the coding done on the formulas, charts, and graphs, but I am uncertain as to how I can create the new tabs. To lay it out another way, here is the order of what I am looking to do:

1. Look for a new value for Department.
2. Create a new tab and label it based on the department name.
3. Execute formulas, charts, and graphs for that department
4. Begin process all over for the next new value for department.

Any help you can provide will be appreciated. Thanks.

Quint

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim c As Range
    Dim List As New Collection
    Dim Item As Variant
    Dim ShNew As Worksheet
    Application.ScreenUpdating = False
'   *** Change Sheet name to suit ***
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A2:A" & Sh.Range("A" & Sh.Rows.Count).End(xlUp).Row)
    On Error Resume Next
    For Each c In Rng
        List.Add c.Value, CStr(c.Value)
    Next c
    On Error GoTo 0
    Set Rng = Sh.Range("A1:C" & Sh.Range("A" & Sh.Rows.Count).End(xlUp).Row)
    For Each Item In List
        Set ShNew = Worksheets.Add
        ShNew.Name = Item
        Rng.AutoFilter Field:=1, Criteria1:=Item
        Rng.SpecialCells(xlCellTypeVisible).Copy ShNew.Range("A1")
        Rng.AutoFilter
    Next Item
    Sh.Activate
    Application.ScreenUpdating = True
End Sub

 

[qvedder]

Thanks Andrew,

One quick question...is there a way to create the new tabs without any data, essentially having blank tabs? Sorry, I am a novice at best when it comes to VBA.

If not, that shouldn't be a problem. I can simply create a macro to clear each tab before running my code for charts and graphs.

Thanks again.

 

[Andrew Poulsom]

Just remove this bit:

        Rng.AutoFilter Field:=1, Criteria1:=Item
        Rng.SpecialCells(xlCellTypeVisible).Copy ShNew.Range("A1")
        Rng.AutoFilter

 

[qvedder]

Thanks again.