Thread: copy worksheet and name it's tab at the same time

Hi,

I'm looking to speed up an operation that is taking awhile because of the number of times it needs to loop. I need to separate about 5000 lines of data into about 100 individual tabs.

I'm currently doing this to prepare my sheets for the data:

Dim rngStaEnd As Range
Set rngStaEnd = DSC.Range("E:E")
Dim intStaEnd As Integer
    intStaEnd = LastInColumn(rngStaEnd)
For l = 1 To intStaEnd Step 1
Tplt100.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = DSC.Cells(l, 5)
Next l

DSC and Tplt100 are code names. I want to copy (and not "add") because I want the new code names to start at Tplt101 and go up from there automatically, so when I'm moving the data around I can do it in a for loop down the lines of data, and move through the sheets one at a time by the code name, starting with Tplt101.

The tab names are named from data taken from all the source data.

The sheet it is copying is empty; I only have it there to start the code names where I want them to start. The above method works great, but it takes twice as long as this method:

Sheets.Add.Name = DSC.Cells(l, 5)

The only problem with this is the code names default to the next standard code name; say at a given time it wants to start at Sheet13, and then go up from there.


What I'd like is to combine the speed of the the sheets.add.name, but copy my specific sheet instead of adding a blank one.

I tried this and it didn't work:

Tplt100.Copy.Name = DSC.Cells(l, 5)

The error I get is:

"Compile error, expected function or variable"



I'm open to other ideas to achieve the speed, such as setting the code name programatically along with Sheets.add, but I don't know if that is possible.

I do know how to change the code name programatically:

ThisWorkbook.VBProject.VBComponents("OldCodeName").Name = "NewCodeName"

But that requires knowing the old code name (can I read the code name of ActiveSheet into a variable?)

This code changes the code name via Sheet index (by variable), which is doable for me:

ThisWorkbook.VBProject.VBComponents(Variable1).Name = "CodeName" & Variable2

But it's too messy because when it copies the old codename + 1, it has a predictable index number, but then when you change the code name the index changes, and the next sheet you copy doesn't have as predictable of a index number.



So this probably won't work, and I've also started to realize this might not be worth the trouble.

BUT--I am still open to ideas if anyone knows how to do this efficiently. Such as; change the code name using the Tab name as the reference of what you are changing. I don't know how to do that...