For j = 1 To UniqueItems(ActiveSheet.Range(TrN_col & "2:" & TrN_col & LastTrN), True)
Worksheets.Add().Name = UniqueItems(ActiveSheet.Range(TrN_col & "2:" & TrN_col & LastTrN), False)(j)
UniqueItems() is a function that returns the number of unique items in a column as an array. currently with the data i'm working with there are two unique items in the column. if I use the
MsgBox() function I get two unique items as I should. Which makes me think that the subroutine is somehow escaped after a new worksheet is added.
In other words how do I get excel to activate the most recently created worksheet?
When you add a new sheet, it becomes the active sheet. After you iterate once.... you will be evaluating
uniqueitems on an emptyworksheet.
I would refactor
uniqueitems to return a collection or dictionary instead of what you are doing.
Results would look a something like:
Dim list as Dictionary set list = UniqueItems(ActiveSheet.Range(TrN_col & "2:" & TrN_col & LastTrN)) For each key in list.keys worksheets.add().name = key next