VBA to find specific names in column, then set a named range (up to 12 dif ranges) - range is variable from day to day

[Tatsu]

hello, I have a data sheet with columns A thru V and 500ish rows, Column A contains a Skill Group name (ie. MCare Contact Center) and then a list of user names in an alias format (ie. jsmith) the number of rows under the MCare Contact Center can change from day to day, anything from 1 line to 200ish lines) at the end of this list is the name of the next skill group (ie. MCare Callbacks), (no gaps or blank lines between the last alias name and skill group name) and again the list may change from day to day, and there will be duplicate alias's from the list above (or below), there are 12 Skill groups in this list, but again this may vary from day to day, down to as little as 6 SG's for the day...all of this is in column A, columns B-V all contain either a number, a percentage or a time value (hh:mm"ss)

What I'm trying to do is either use VBA to to find the Skill group name(s) and create a named range for each skill group set.
or,
a vba to pull the info from each skill group set and paste to separate tabs on a different workbook.

currently I'm throwing in some conditional formatting to highlight the skill group names, then manually setting the named ranges, this wasn't a horrible task until we found a need to re-run the daily reports for that last 4 months, so basically about 120 different reports to set up.

From there i build formulas to grab specific info from the named ranges to feed a Key Performance Indicator report

I'd be eternally grateful for any advice.

 

[BrianJN1]

I understand that you have a reasonable command of VBA and so I’m wondering if you are able to create a sheet that only has the Skill Group Names?

From there VBA would compare UserNames against your listed SG Names, but when suddenly the next evaluation is an SG Name, you exit that loop and being another with that new SG Name until you encounter another.

Does that seem to work for you?

 

[Tatsu]

thanks for the responce, yes, I could easily set up a reference sheet, what I'm hoping for is a point in the right direction to see an existing vba example...I know just enough of vba to take an existing code and tweek it enough to work, for me to write one from scratch is litterly learning to speak a new language

 

[Fluff]

Give this a go

Sub CreateNamedRange()

    Dim Rng As Range
    Dim Cl As Range
    
    With Sheets("[COLOR=#ff0000]Data[/COLOR]")
        For Each Cl In Sheets("[COLOR=#0000ff]Test[/COLOR]").Range("A1", Sheets("[COLOR=#0000ff]Test[/COLOR]").Range("A" & Rows.Count).End(xlUp))
           Set Rng = .Columns(1).Find(Cl.Value, .Range("A1"), xlFormulas, xlWhole, xlByRows, xlNext, False, False)
           If Not Rng Is Nothing Then Rng.EntireRow.Insert xlDown
        Next Cl
        Set Rng = Nothing
        For Each Rng In .Columns(1).SpecialCells(xlConstants).Areas
            If Not Rng.Count = 1 Then Rng.Resize(, 22).Name = Replace(Rng.Resize(1, 1).Value, " ", "")
        Next Rng
        .Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
    End With
    
End Sub

Sheet name in red needs to be changed to match the name of your data sheet.
The sheet name in Blue needs to be changed to match the sheet with your Skill Group Names. The SG names need to be entered in A1 downwards, one name per cell.
It assumes that your data sheet has a header row in row 1

 

[Fluff]

Should also have mentioned that the named range will be the SG name minus spaces.
So will not work if you have invalid characters as part of your SG names.

 

[Tatsu]

Absolutely amazing, I was not expecting that much effort to go into the responce!, call me a fan for life!!!, I will give it a try this evening.

 

[Tatsu]

Absolutely amazing, I was not expecting that much effort to go into the responce!, call me a fan for life!!!, I will give it a try this evening.

Worked perfectly, I cannot express my gratitude enough, I'm going to play with this to see if I can figure out how to put the "test" page on a separate workbook and link to that, and you were correct with the header line, except I failed to mention the 11 lines of garbage at the top of the page, (thanks to a horrible export from our new system), but I assumed by your comment I should get rid of the garbage except for a top "header" line...worked perfect, and when I went back to do a test with the 11 lines of garbage in place I got a debug error...pretty confident I can figure out which part of your code points to that piece. as for the spaces in the skill group, names that too was pretty simple to get rid with a find/replace...I can macro in all these for a quick resolution for now so I can get through this project, then I can take the time to try and figure out how you did this amazing code!!! but again, Thank You so much for your generous help!!

 

[Fluff]

Glad to help & thanks for the feedback.
If there's anything you can't figure out, just shout