Help with code

[Ronbo]

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]

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

 

[Don Guillett]

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

 

[Ronbo]

It still errors on s2=sName

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

 

[Jeff]

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

 

[Jeff]

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)

 

[Gord Dibben]

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

 

[Bernard Liengme]

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

 

[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

 

[Ronbo]

Thanks to all for the help.

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




.

 

[Chip Pearson]

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]