Hey all. I'm running a script where I want to create a defined name that varies based on my counter. I thought below will work, but when I get to the "ActiveWorkbook.Names.add Name: = " I get errors when attempting to use the variable TeamName. I thought the "" & TeamName, would work but doesnt.
The code:
While Counter < NumEmpTeam + 1
'Count number of employees on active EmpTeam Counter
NumEmp = Application.WorksheetFunction.CountA(Sheets("Employee Teams").Range(Cells(2, vCol), Cells(100, vCol))) + 1
Set EmpNames = Range(Cells(2, vCol), Cells(NumEmp, vCol))
TeamName = Cells(1, vCol).Value
ActiveWorkbook.Names.Add Name:="" & TeamName , RefersToR1C1:="='Employee Teams'!R2C" & vCol & ":R" & NumEmp & "C" & vCol
Counter = Counter + 1
vCol = vCo`l + 1
Wend
I've also tried substituting the activeworkbooknames for this:
EmpNames.Name = "" & TeamName
or some combination but it doesn't work.
Anyone have any ideas?
Thanks in advance
The code:
While Counter < NumEmpTeam + 1
'Count number of employees on active EmpTeam Counter
NumEmp = Application.WorksheetFunction.CountA(Sheets("Employee Teams").Range(Cells(2, vCol), Cells(100, vCol))) + 1
Set EmpNames = Range(Cells(2, vCol), Cells(NumEmp, vCol))
TeamName = Cells(1, vCol).Value
ActiveWorkbook.Names.Add Name:="" & TeamName , RefersToR1C1:="='Employee Teams'!R2C" & vCol & ":R" & NumEmp & "C" & vCol
Counter = Counter + 1
vCol = vCo`l + 1
Wend
I've also tried substituting the activeworkbooknames for this:
EmpNames.Name = "" & TeamName
or some combination but it doesn't work.
Anyone have any ideas?
Thanks in advance