With the following code I get the error message " "Type mismatch" on "Set
s2". What am I doing wrong?

Dim s1 As Worksheet, s2 As Worksheet
Dim sName As String

sName = Range("n7")
Worksheets.Add(After:=Sheets(Sheets.Count)).Name =
ActiveSheet.Range("n7").Value

Set s1 = ActiveSheet
Set s2 = sName

Thanks,
Ronbo

Remove Set from Set s2 = sName
s\b s2=sName
no need to set string data types.
"Ronbo" wrote:

With the following code I get the error message " "Type mismatch" on "Set
s2". What am I doing wrong?

Dim s1 As Worksheet, s2 As Worksheet
Dim sName As String

sName = Range("n7")
Worksheets.Add(After:=Sheets(Sheets.Count)).Name =
ActiveSheet.Range("n7").Value

Set s1 = ActiveSheet
Set s2 = sName

Thanks,
Ronbo

Sub SimpleAddSheetandNameforCell()
sheets.Add(After:=Sheets(Sheets.Count)).Name = range("n7")
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ronbo" wrote in message
...
With the following code I get the error message " "Type mismatch" on "Set
s2". What am I doing wrong?

Dim s1 As Worksheet, s2 As Worksheet
Dim sName As String

sName = Range("n7")
Worksheets.Add(After:=Sheets(Sheets.Count)).Name =
ActiveSheet.Range("n7").Value

Set s1 = ActiveSheet
Set s2 = sName

Thanks,
Ronbo

It still errors on s2=sName

"Jeff" wrote:

Remove Set from Set s2 = sName
s\b s2=sName
no need to set string data types.
"Ronbo" wrote:

With the following code I get the error message " "Type mismatch" on "Set
s2". What am I doing wrong?

Dim s1 As Worksheet, s2 As Worksheet
Dim sName As String

sName = Range("n7")
Worksheets.Add(After:=Sheets(Sheets.Count)).Name =
ActiveSheet.Range("n7").Value

Set s1 = ActiveSheet
Set s2 = sName

Thanks,
Ronbo

I assume you're trying to add a new sheet after the last sheet and name it.
Right?


"Ronbo" wrote:

It still errors on s2=sName

"Jeff" wrote:

Remove Set from Set s2 = sName
s\b s2=sName
no need to set string data types.
"Ronbo" wrote:

With the following code I get the error message " "Type mismatch" on "Set
s2". What am I doing wrong?

Dim s1 As Worksheet, s2 As Worksheet
Dim sName As String

sName = Range("n7")
Worksheets.Add(After:=Sheets(Sheets.Count)).Name =
ActiveSheet.Range("n7").Value

Set s1 = ActiveSheet
Set s2 = sName

Thanks,
Ronbo

Dim s1 As Worksheet, s2 As Worksheet
Dim sName As String

sName = Range("n7")
Worksheets.Add(After:=Sheets(Sheets.Count)).Name =
ActiveSheet.Range("n7").Value

Set s1 = ActiveSheet
Set s2 = Worksheets(sName)



"Ronbo" wrote:

It still errors on s2=sName

"Jeff" wrote:

Remove Set from Set s2 = sName
s\b s2=sName
no need to set string data types.
"Ronbo" wrote:

With the following code I get the error message " "Type mismatch" on "Set
s2". What am I doing wrong?

Dim s1 As Worksheet, s2 As Worksheet
Dim sName As String

sName = Range("n7")
Worksheets.Add(After:=Sheets(Sheets.Count)).Name =
ActiveSheet.Range("n7").Value

Set s1 = ActiveSheet
Set s2 = sName

Thanks,
Ronbo

Your biggest problem is that you just added a new sheet which is now the
activesheet.

ActiveSheet.Range("n7") has no value on this new sheet so sName bombs

Sub test()
Dim sName As String
sName = Range("n7")
Worksheets.Add(After:=Sheets(Sheets.Count)).Name = sName
End Sub


Gord Dibben MS Excel MVP

On Fri, 29 Jan 2010 10:14:01 -0800, Ronbo
wrote:

With the following code I get the error message " "Type mismatch" on "Set
s2". What am I doing wrong?

Dim s1 As Worksheet, s2 As Worksheet
Dim sName As String

sName = Range("n7")
Worksheets.Add(After:=Sheets(Sheets.Count)).Nam e =
ActiveSheet.Range("n7").Value

Set s1 = ActiveSheet
Set s2 = sName

Thanks,
Ronbo

You have been given great help by others but here is my small contribution
Experiment with this code

Sub tryme()
'Give a name to a range of cells (in this case a single cell)
Set s2 = Range("N7")
For Each mycell In s2
MsgBox mycell
Next
MsgBox s2.Count

'Give a name to a range of cells
Set s3 = Range("N7:N10")
For Each mycell In s3
MsgBox mycell
Next
MsgBox s3.Count

'Take the value from N7 and place it in the variable Sname
Sname = Range("N7")
MsgBox Sname

End Sub

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Ronbo" wrote in message
...
With the following code I get the error message " "Type mismatch" on "Set
s2". What am I doing wrong?

Dim s1 As Worksheet, s2 As Worksheet
Dim sName As String

sName = Range("n7")
Worksheets.Add(After:=Sheets(Sheets.Count)).Name =
ActiveSheet.Range("n7").Value

Set s1 = ActiveSheet
Set s2 = sName

Thanks,
Ronbo

Jeff's last response errored on "worksheet.add" and as for Don's response, I
do not have a problem getting a new worksheet with the code I have. The
problem I have is referencing the new worksheet. It's name continually
changes and I need a way to reference it for futher use in the routine. The
original code had

Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")

I used that for simplicity believing it would be easy to change afterwards,
but it has not been easy. s1 is easy in that it is always the "ActiveSheet",
but sheet2 is a new sheet added each time with a differant worksheet name
that I need to refer to later on in the routine. Using the initial code in
this post it errors as previously stated. I hope this helps.

Thanks for your help.
Ronbo







"Don Guillett" wrote:

Sub SimpleAddSheetandNameforCell()
sheets.Add(After:=Sheets(Sheets.Count)).Name = range("n7")
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ronbo" wrote in message
...
With the following code I get the error message " "Type mismatch" on "Set
s2". What am I doing wrong?

Dim s1 As Worksheet, s2 As Worksheet
Dim sName As String

sName = Range("n7")
Worksheets.Add(After:=Sheets(Sheets.Count)).Name =
ActiveSheet.Range("n7").Value

Set s1 = ActiveSheet
Set s2 = sName

Thanks,
Ronbo

.

Thanks to all for the help.

"Bernard Liengme" wrote:

You have been given great help by others but here is my small contribution
Experiment with this code

Sub tryme()
'Give a name to a range of cells (in this case a single cell)
Set s2 = Range("N7")
For Each mycell In s2
MsgBox mycell
Next
MsgBox s2.Count

'Give a name to a range of cells
Set s3 = Range("N7:N10")
For Each mycell In s3
MsgBox mycell
Next
MsgBox s3.Count

'Take the value from N7 and place it in the variable Sname
Sname = Range("N7")
MsgBox Sname

End Sub

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Ronbo" wrote in message
...
With the following code I get the error message " "Type mismatch" on "Set
s2". What am I doing wrong?

Dim s1 As Worksheet, s2 As Worksheet
Dim sName As String

sName = Range("n7")
Worksheets.Add(After:=Sheets(Sheets.Count)).Name =
ActiveSheet.Range("n7").Value

Set s1 = ActiveSheet
Set s2 = sName

Thanks,
Ronbo

.

Set s2 = sName

doesn't work because sName is a String and S2 is a Range. Set only
works with object type variables and then only if the objects are
compatible. An S2 defined as an object requires that it be Set to a
Worksheet object. Anything else will cause a compiler error or a
runtime error, depending on the circumstances.

sName = Range("n7")

This line of code uses the default Value property, so it executes as

sName = Range("n7").Value

What would sName and S2 be if your code were correct?

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Fri, 29 Jan 2010 10:14:01 -0800, Ronbo
wrote:

With the following code I get the error message " "Type mismatch" on "Set
s2". What am I doing wrong?

Dim s1 As Worksheet, s2 As Worksheet
Dim sName As String

sName = Range("n7")
Worksheets.Add(After:=Sheets(Sheets.Count)).Nam e =
ActiveSheet.Range("n7").Value

Set s1 = ActiveSheet
Set s2 = sName

Thanks,
Ronbo