CODEWITHSUNDEEP
arraysexcelvba
Philip
Philip

Reputation: 13

Auto create Excel sheets from Array values

I am relatively new to vba. I have used VB a long time ago so I derive a lot of info from that experience. Though now I'm facing a harder task and I don't quite know how to do it.

I have got a data sheet with in column E software version information (ie "3.1.1", "3.1.2" and so on). I have created a for loop the searches through E. In this for there are several if statements like this one:

If Cells(r, Columns("E").Column).Value = "3.1.2" Then 'find criteria

            'Copy the current row
            Rows(r).Select
            Selection.Copy

            'Switch to the sprint where you want to paste it & paste
            Sheets("Sprint 2").Select
            Rows(sprint2).Select
            ActiveSheet.Paste

            sprint2 = sprint2 + 1 'next row

            'Switch back to backlog & continue to search for criteria
            Sheets("Backlog").Select
ElseIf...

This is working fine for me, except that I need to create the sheets before running the macro. What I would like to do is:

  1. Search through column E
  2. Fill an array with all unique values in Column E*[edit]
  3. Create a sheet for every value in the Array

I would love to hear what you guys think.

Upvotes: 1

Views: 1799

Answers (1)

MiVoth
MiVoth

Reputation: 1012

Perhaps that helps:

Sub ColumnE()
Dim colE As Long, r As Long, c As Object, exists As Boolean
Dim values As Collection, i As Long
Set values = New Collection
colE = Columns("E").Column
r = Cells(Rows.Count, colE).End(xlUp).Row
For i = 1 To r ' step 1: loop through column E
    exists = False
    For Each c In values ' step 2: look in collection if the element was already inserted
        If c = Cells(i, colE) Then
            exists = True
            Exit For
        End If
    Next c
    If Not exists Then values.Add Cells(i, colE)
Next i
For Each c In values ' step 3: add a sheet for every value in collection
    Worksheets.Add  ' WARNING: you should test, if there already is a sheet with that name
    ActiveSheet.name = c
Next c
End Sub

I like to use collections more than arrays in vba, because i can dynamically add new elements without resizing. (but it depends on the situation...)

Upvotes: 1

Related Questions