Syntax must be incorrect for range reference

rmunn

New Member
Joined
Jan 28, 2010
Messages
10
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

Well-known Member
Joined
Jan 26, 2005
Messages
690
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?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

wolverineb

Well-known Member
Joined
Jan 26, 2005
Messages
690
Try this:

Code:
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

New Member
Joined
Jan 28, 2010
Messages
10
Wolverineb: I ran your code, and got the msgbox: $A9$B9$14, and then the same results as the original code.
 

wolverineb

Well-known Member
Joined
Jan 26, 2005
Messages
690

ADVERTISEMENT

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

rmunn

New Member
Joined
Jan 28, 2010
Messages
10
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

Well-known Member
Joined
Jan 26, 2005
Messages
690

ADVERTISEMENT

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.
 

rmunn

New Member
Joined
Jan 28, 2010
Messages
10
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

Well-known Member
Joined
Jan 26, 2005
Messages
690
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.
 

Forum statistics

Threads
1,148,179
Messages
5,745,201
Members
423,932
Latest member
pablo2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top