Syntax must be incorrect for range reference

[rmunn]

The following sub to create and name wsheets results naming the new sheet with the value of "A9" only, but what I want to name the new sheet is "A9" + "B9". Where have I gone wrong?
Thanks.

Sub CreateWorksheets()
Dim newSheet As Worksheet, itemSheet As Worksheet
Dim cell As Object
Dim itemrange As String

Set itemSheet = Sheets("BIDFORM")

Application.ScreenUpdating = False
itemrange = "A9:B9:" & itemSheet.Range("A9").End(xlDown).Address
For Each cell In itemSheet.Range(itemrange)
If SheetExists(cell.Value) = False Then
Sheets.Add Before:=Sheets("BACK SHEET"), _
Type:="C:\Path\File"
Set newSheet = ActiveShee
newSheet.Name = cell.Value

End If
Next cell
Application.ScreenUpdating = True

End Sub

 

[wolverineb]

so if there are values in rows 9 through 15, you would want six new worksheets, each with a name that was the concatenation of the contents of column A and B in that row?

 

[rmunn]

Precisely.

 

[wolverineb]

Try this:

Sub CreateWorksheets()
    Dim newSheet As Worksheet, itemSheet As Worksheet
    Dim cell As Object
    Dim itemrange As Range

    Set itemSheet = Sheets("BIDFORM")

    Application.ScreenUpdating = False
    Set itemrange = itemSheet.Range("A9").Resize(itemSheet.Range("A9").End(xlDown).Row - 8, 1)
    For Each cell In itemrange
        If SheetExists(cell.Value & cell.Offset(0,1).Value) = False Then
            Sheets.Add Before:="BACK SHEET", _
                       Type:="C:\Path\File"
            ActiveSheet.Name = cell.Value & cell.Offset(0,1).Value

        End If
    Next cell
    Application.ScreenUpdating = True

End Sub

 

[rmunn]

Wolverineb: I ran your code, and got the msgbox: $A9$B9$14, and then the same results as the original code.

 

[wolverineb]

I think I edited it after you copied. Try again pls. (I had pasted in an intermediate version)

 

[rmunn]

I thought you had it, but not quite. It named the first inserted sheet correctly, but the second sheet was named B9,C9, and the third produced an error prompt that a shtname can occur only once.

The msgbox was "$A$9:$B$14

 

[wolverineb]

Just to be sure, you did recopy and paste the code above, right? There should no longer be any msgbox and the code is different elsewhere as well.

It totally works for me, so your cell contents must be different than I am assuming.

 

[wolverineb]

Can you show me what's in A9:B14 on your sheet?

 

[rmunn]

That did it. I thank you profusely. Incidentally, your code has an error in the sheets.add line, you ommitted "sheets" and the () around "BACK SHEET".

I really appreaciate your patience and help.

 

[wolverineb]

No problem.

I had to modify that line of code when testing since I don't have the BACK SHEET or the Type template. Forgot to put it all back properly.