Copy Sheets based on List

[temerson]

I have a sheet with the columns below:
[TABLE="width: 186"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]DC[/TD]
[TD]ITEM #[/TD]
[/TR]
[TR]
[TD]DC 1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]DC 2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]DC 3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]DC 3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]DC 3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]DC 4[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


I would like to do the following:

1. copy the 'master sheet' based on the number of unique values in Column "DC". In this case, this would be 4 additional sheets.
2. name the sheets based on this list (which I can do but including this for a wholesome picture)
2. delete any rows that does not belong in a given tab. (e.g. tab DC 1 will only have one row, for Item # 1. Tab DC 3 will have 3 rows)

Thank you in advance!

 

[DanteAmor]

Try this

I guess you have another sheet with the "DC" list.
Change "list" to the name of that sheet.

Sub Test()
  Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, dict As Object, num As Long, ky As Variant
  Set sh1 = Sheets("[COLOR=#ff0000]list[/COLOR]")
  Set sh2 = Sheets("[COLOR=#ff0000]Master sheet[/COLOR]")
  Set dict = CreateObject("scripting.dictionary")
  For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
    If Not dict.Exists(c.Value) Then
      dict(c.Value) = 1
    Else
      num = dict(c.Value) + 1
      dict(c.Value) = num
    End If
  Next
  For Each ky In dict.Keys
    sh2.Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = ky
    num = dict(ky)
    ActiveSheet.Rows(num + 1 & ":" & Rows.Count).ClearContents
  Next ky
End Sub

Sheet list
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >DC</td><td >ITEM #</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >DC 1</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >DC 2</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >DC 3</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >DC 3</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >DC 3</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >DC 4</td><td style="text-align:right; ">1</td></tr></table>