Tabs Renamed as Cells Have the Same Name

[OldSoldier]

Hi. I have this macro that names worksheets after each of the contents of a list.

The list drives output on a master worksheet related to the particular items from the list selected.

The macro selects each item and copies the contents of each filtered result into its own worksheet and renames that sheet after the contents of cell A2.

The problem is that the term in cell A2 is sufficiently long that the tabs end up having the same name and the macro errors out.

Here is the code:

Dim NewSheet As Worksheet
Dim Cell As Range
For Each Cell In Range("Combined")
Set NewSheet = Sheets.Add(After:=Sheets(Sheets.Count))
Sheets("Report").Range("ReportArea").Copy NewSheet.Range("A1")
NewSheet.Range("A2").Value = Cell.Value
NewSheet.Name = Left(NewSheet.Range("A2").Value, 31)
Sheets("Report").Range("StaffCounts").Copy
NewSheet.Range("U1").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Next Cell
End Sub

Can anyone help with this?

Thanks, OldSoldier

 

[OldSoldier]

I worked around this by making shorter names (they necessarily have to be pretty descriptive); however I find that the macro errors when I get down to the end of the list. It makes a new sheet and then breaks.

Thanks anyone!

 

[xenou]

Don't know how many sheets you are copying but there's an old known issue with copying too many sheets programmatically:
http://support.microsoft.com/kb/210684

 

[Peter_SSs]

1. What exactly is the error message when the code 'breaks'?

2. When it does 'break' and you click 'Debug', what line of code is highlighted?

3. Tell us more about the named range "Combined". Which sheet? What range? What data (at least some of it if it is a large range)?