VBA: Create tab for each unique occurence in column F

[Rowland Hamilton]

<TABLE cellSpacing=0 cellPadding=2 width="100%" border=0><TBODY><TR><TD align=left>Folks: VBA: How do I create an array for the unique occurences in column F of my spreadsheet? I want to add worksheets with tab name labeled after each unique occurence, skip blanks. - Thank you, Rowland Hamilton</TD></TR></TBODY></TABLE>

 

[AlphaFrog]

Don't want to referr to all sheets like "*Natives" because user could create a sheet with same naming convention that I don't want to operate on, and I already created the sheets, generated the sheet names to begin with, so

Replace:

For Each ws In ActiveWorkbook.Sheets
            If ws.Name Like "*Natives" Then

You already know the names of the sheets.

    For Each cell In rngUniques
        With Sheets(cell.Value & " Natives")
            [COLOR="Green"]' Do something with each sheet here[/COLOR]
        End With
    Next cell

Or you already added code (red) that references the new sheets.

                Sheets.Add(After:=Sheets(Sheets.Count)).Name = cell.Value & " Natives"
                    [COLOR="Red"]Range("a1").Formula = "Pillar-Ledger"
                    Range("h1").Formula = "LU Col"
                    Range("i1").Formula = "Total P&L"
                    Range("a2").Value = cell.Value[/COLOR]

When the new sheet is .added, it becomes the currently ActiveSheet. If you do all you copy\paste\formattng at this point before adding the next new sheet, you coul reference the ActiveSheet or reference Sheets(cell.Value & " Natives")

 

[AlphaFrog]

Perhaps instead of making this a range in the beginning:

Set rngUniques = HCCosts.Range("E2:E" & Lastrow).SpecialCells(xlCellTypeVisible)

Could it be set as an array right off the bat, then referred to in later code?

Even if there is better way, I still want to learn how to capture the unique occurences and turn them into an actual array I can referr to throughout the code.

You could capture the uniques into an array by looping trough the rngUniques and add them to an array. I don't see the advantage in using an array versus a range for your situation. You can refernce rngUniques throughout the code. The sheet HCCosts doesn't have to be active or selected to reference rngUniques.

 

[Rowland Hamilton]

Alpha Frog:

Thought rngUnique was not a set of captured and stored values and that if I filter the source data a different way in my code, it would cause problems. Clearly it doesn't as I tested the code with this modification:

For Each cell In rngUniques
        Set ws = Sheets(cell.Value & " Natives")
        With ws
              'code here
         End with

Was also unsure that could refer in and out of a with statement for copy/ paste between sheets but obviously you can. Keeping ws was easiere since it was written already and only requires 2 characters to referr to the sheet.

Thank you - Rowland