refer to dynamic named range with vba

opislak

New Member
Joined
Feb 28, 2017
Messages
45
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?

VBA Code:
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Team = Inputbox("Wich team (###)?")
Range("A1").AutoFilter Field:=1, Criteria1:="<>HRRef" & Team & "*"
 

opislak

New Member
Joined
Feb 28, 2017
Messages
45
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
What are the names of the sheets?
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,931
Messages
5,639,064
Members
417,067
Latest member
rohitbabshet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top