refer to dynamic named range with vba

[opislak]

Hi all,

I have a workbook with different sheets. The sheets contain names of team members and the sheet names are named after the Team Leads. The data on each sheet should be selected from a sheet with raw data, according to the name of the sheet/team lead.
For that purpose, I created a separate sheet called "TeamLeads" with a list of Team Leads and the Team's group names they are responsible of. I named the table "TLs". In column A is the name of the Team Lead (e.g. "John" and "Nancy"), in column B the groups they are responsible of (e.g. John is boss of the groups with HR code 100, 100-1, 100-2, etc and Nancy leads groups starting with "200").

How do I proceed in VBA to refer from a sheet to the table in order to select only the names of the respective team members.

Below is the relevant part of the code I have so far and that works fine.
- it starts by copying the raw data and pasting it starting from cell A3 to E....
- then it selects team members with the HR-code "100*" (100, 100-1, 100-2, etc.)
- it finally deletes all other employees
In another worksheet I could use "200*" instead of "100*", etc. But how to dynamically do that?

Sub TeamLead()

      Range("A3", Cells(Range("A2"), 4)).ClearContents
      RawData.Copy
      ws.Range("A3").Select
      ActiveSheet.Paste
      Application.CutCopyMode = False

     Range("A1").AutoFilter Field:=1, Criteria1:="<>HRRef100*"
     Range("A3:A" & lastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
     ws.AutoFilterMode = False

End Sub

Thanks in advance!!
Patrick.

 

[mart37]

Team = Inputbox("Wich team (###)?")
Range("A1").AutoFilter Field:=1, Criteria1:="<>HRRef" & Team & "*"

 

[opislak]

Team = Inputbox("Wich team (###)?")
Range("A1").AutoFilter Field:=1, Criteria1:="<>HRRef" & Team & "*"

That works, thank you.
I will try to avoid the input box and copy the names of the sheets one by one instead.

 

[mart37]

What are the names of the sheets?

 

[opislak]

What are the names of the sheets?

The sheets are named after the team leads (e.g. John, Philip, Nancy, etc)

 

[mart37]

Sub TeamLead()
    Dim RawData As Range
    Dim TeamLeads As Worksheet, ws As Worksheet
    Dim LastRow As Long, x As Long, LastRowWs As Long
    Dim Team As String, Name As String
    Set RawData = Worksheets("RawData").Range("A1:E19")
    Set TeamLeads = Worksheets("TeamLeads")
    With TeamLeads
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        For x = 2 To LastRow
            Name = .Cells(x, 1)
            Team = .Cells(x, 2)
            With Worksheets(Name)
                .Range(.Range("A3"), .Range("A3").SpecialCells(xlLastCell)).ClearContents
                RawData.Copy .Range("A3")
                .Range("A3").AutoFilter Field:=1, Criteria1:="<>HRRef" & Team & "*"
                LastRowWs = .Range("A:A").Find(What:="*", After:=.Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                .Range("A3:A" & LastRowWs).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                .AutoFilterMode = False
            End With
        Next
    End With
End Sub