Batch Tab Creation

I am trying to set up a spreadsheet and associated tabs.

My values are all found in the A column (say from 12 until the cell content is empty)

The cell contents are provided by the formula A12+10

When I use the following code everything works great if I do one row/sheet at a time but if I want to do 10 or 100 sheets through a copy paste into column A the only sheet that is named correctly is the last one and the intervening sheets all return to titles of sheetXX

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.ScreenUpdating = False
Dim rngCell As Range, rngIntersect As Range
On Error Resume Next

Set rngIntersect = Intersect(Target, Range("A12:A1012")) 'change this if neccessary to allow for more cells
If rngIntersect Is Nothing Then
Exit Sub
End If

For Each rngCell In rngIntersect.Cells
If rngCell.Value <> "" Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Sheet1.Range("A65536").End(xlUp).Value
Sheets("Sheet#").Select 'change "sheet1" to the actual bid item number
End If
Next rngCell
Application.ScreenUpdating = True
End Sub


Any thoughts would be greatly appreciated.

Welcome to the forum!

The moderator will probably ask that you put your code in code tags as explained in the rules and the FAQ.

It is not clear to me where your sheetnames will come come from. See my commented line for an alternative. Of course it does not check for duplicate sheet names.

Maybe:

Please Login or Register  to view this content.

Kenneth,

That little tweak did it. Thank you.