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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

wolverineb

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

itemrange = "A9:B9:" & itemSheet.Range("A9").End(xlDown).Address

to this

itemrange = "A9:" & itemSheet.Range("A9").End(xlDown).Address
 

rmunn

New Member
Joined
Jan 28, 2010
Messages
10
Thanks for your quick response. I have already tried your suggestion, but the results are the same.
 

GeofW

Active Member
Joined
May 14, 2002
Messages
286

ADVERTISEMENT

I didn't think you could name a sheet A9 + B9, but you can. I think you want to say
Code:
newSheet.Name = cell.Value & " + " & cell.offset(0,1).Value
 

rmunn

New Member
Joined
Jan 28, 2010
Messages
10
I tried GeofW's suggestion, and strangely the result was literally B9 + A9, meaning that the position of A9 and B9 were reversed, and the + also appeared in the result.
 

wolverineb

Well-known Member
Joined
Jan 26, 2005
Messages
690

ADVERTISEMENT

That's what your question says, literally. :)

Did you try the xlToRight thing?
 

wolverineb

Well-known Member
Joined
Jan 26, 2005
Messages
690
You should post your worksheet contents. Hard to tell what you really want (as you can see from GeofW's answer, which was logical but maybe not what you want).
 

rmunn

New Member
Joined
Jan 28, 2010
Messages
10
Perhaps I should have been more clear. What I am trying to do is to loop through cols A & B and combine those values at each row, with the resulting string being inserted as the name of the newly inserted sheet.
I have no problem with the inserting of the sheets, but can't get the name to include the combined value. When I run what I posted, the first sheet inserted was named A9, and the second sheet inserted was named B9, and so on down the columns.
 

Forum statistics

Threads
1,148,176
Messages
5,745,191
Members
423,931
Latest member
thangvan114

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