# Creating New Tabs from Values in Raw Data with Macro

#### qvedder

##### New Member
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.

Quint

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Try:

Code:
``````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
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
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``````

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.

Just remove this bit:

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

Thanks again.

Replies
1
Views
528
Replies
0
Views
167
Replies
1
Views
395
Replies
2
Views
243
Replies
1
Views
277

1,203,468
Messages
6,055,595
Members
444,800
Latest member
KarenTheManager

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back