Adding Sheets from array

[ashleyuk1984]

Hi,
I'm sure there are loads of ways to do this, but I think the fastest way would be to use an array, and sorting through the list, and storing only unique values in the array.

I have a list of references on sheet1, in column A.
The references look something like this:

RE11111
RE22222
RE33333
AD11111
AD22222
OE11111
ME11111
ME11111

I need to create/rename the sheets, using the first 3 characters of the reference (Starting from Sheet 2 (renaming Sheet1 as RAW DATA))

Sheet2 would be called RE1
Sheet3 would be called AD1
Sheet4 would need to be added, and called OE1
Sheet5 would need to be added, and called ME1

At the moment, I can't really use a static list of references within the code, as the references in the list change from time to time, and new ones are being added.
So I need to look into creating a more dynamic method.

I haven't learnt arrays before, so this might be a good starting point for me.

Thank You

[snb]

Why not posting a sample file ?

For more on arrays see:

http://www.snb-vba.eu/VBA_Arrays_en.html

[ashleyuk1984]

Typical!!
I've just found a piece of code which allows me store unique values in a range.

I've modified it to suit my requirements.
The only thing that it doesn't do is rename sheets 2 or 3... Although, I could easily get round this by just deleting those sheets at the start.

Sub AddingSheetsFromArray()
Dim tmp As String
Dim arr() As String
Dim LastRow As Integer


LastRow = Range("A" & Rows.Count).End(xlUp).Row


For Each cell In Range("A2:A" & LastRow)
   If (Left(cell, 3) <> "") And (InStr(tmp, Left(cell, 3)) = 0) Then
     tmp = tmp & Left(cell, 3) & "|"
   End If
Next cell


If Len(tmp) > 0 Then tmp = Left(tmp, Len(tmp) - 1)


arr = Split(tmp, "|")


For x = 0 To UBound(arr)
    Sheets.add(after:=Sheets(Sheets.Count)).Name = arr(x)
Next x


End Sub

@snb,
Thanks for your link. I'll take a look and try to learn a bit more about arrays.

I think for now, as I can just delete sheets 2 and 3, I'll mark this as solved. Feel free to post improvements
Thanks for looking.

[snb]

Keep it simple:

Sub M_snb()
   sheet1.usedrange.columns(1).offset(1).advancedfilter 2,,sheet1.cells(1,20),true
   sn=sheet1.columns(20).specialcells(2)
   sheet1.columns(20).clearcontents

   for each it in sn
     sheets.add(,sheets(sheets.count)).name=it
   next
End Sub