Solved: Create a worksheet for each value in Column A

[marshybid]

Hi All,

Can somebody please help me with the following problem. I have attached a really basic example sheet.

I need to add functionality to my macro. I would like to be able to loop through all values in Column A and for each new value create a worksheet with the worksheet then being named as the value.

In my actual spreadsheet there will be multiple rows in cloumn A with the same value, but I just need one worksheet for each unique value.

Hopefully this makes sense.

Thanks,

Marshybid

[figment]

there may be a better way to do this, but this appears to work for me

Sub samp()
Dim a As Long, nworks As Worksheet
With Worksheets("Data")
For a = 2 To .Range("A1").End(xlDown).Row
If Need_Worksheet(.Range("A" & a)) Then
Set nworks = ActiveWorkbook.Worksheets.Add
nworks.Name = .Range("A" & a)
End If
Next
End With
End Sub

Function Need_Worksheet(str As String) As Boolean
Need_Worksheet = True
Dim works As Worksheet
For Each works In ActiveWorkbook.Worksheets
If works.Name = str Then
Need_Worksheet = False
End If
Next
End Function

[Bob Phillips]

Sub AddSheets()
Dim LastRow As Long
Dim cell As Range
Dim sh As Worksheet

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A1").Resize(LastRow)

.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
End With

On Error Resume Next
For Each cell In .Range("A2").Resize(LastRow - 1).SpecialCells(xlCellTypeVisible)

.Parent.Worksheets.Add(after:=.Parent.Worksheets(.Parent.Worksheets.Count)) .Name = cell.Value
Next cell

.Activate
.ShowAllData
End With
End Sub

[Simon Lloyd]

Bob, that's the way i would have gone but why use Resize rather than:

.Range("A2:A" & .Range("A" & rows.count).End(xlUp).row-1)

[Bob Phillips]

Don't know really, maybe I just wanted a change.

[Simon Lloyd]

:rotlaugh: I thought perhaps it was a much smarter way of encompassing the range!

[marshybid]

Thank you to each of you for your great input.

xld, I will use your method as I find it easier to understand and therefore edit later on.

Not sure that I actually understand the resize component??

Marking as solved

Marshybid