当前位置: 动力学知识库 > 问答 > 编程问答 >

vba - add more than one worksheet to excel using for loops

问题描述:

For j = 1 To UniqueItems(ActiveSheet.Range(TrN_col & "2:" & TrN_col & LastTrN), True)

With Workbook

Worksheets.Add().Name = UniqueItems(ActiveSheet.Range(TrN_col & "2:" & TrN_col & LastTrN), False)(j)

End With

Next 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.

Any ideas?

EDIT:

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
分享给朋友:
您可能感兴趣的文章:
随机阅读: