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