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?
Thanks in advance!!
Patrick.
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.