PDA

View Full Version : [SOLVED] Adding Sheets from array



ashleyuk1984
12-12-2014, 03:37 AM
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
12-12-2014, 03:54 AM
Why not posting a sample file ?

For more on arrays see:

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

ashleyuk1984
12-12-2014, 03:58 AM
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
12-12-2014, 04:44 AM
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