Hi JH,

Try something like:

Sub Tester01()
Dim cell As Range
For Each cell In Range("MyList").Cells
With cell
Sheets.Add.Name = IIf(Len(.Value) 31, _
Left(.Value, 31),
..Value)
End With
Next cell

End Sub


---
Regards,
Norman



"JH" wrote in message
...
Hello,

I just found an unexpected problem.
I have a sheet with a list of suppliers
I create new sheets in my macro that are called after suppliers. One sheet
one supplier.
And the problem occurs when I'm creating new sheets.
Some supplier's names are longer than 31 char. and sheet's name cannot be
longer than 31 characters.If so run-time errror occurs
I'd need to validate supplier's name before creating the new sheet.
If that name is longer than 31 char I would like to shorten supplier's
name
so that is shorter than 31 char and I don't know how.

can anybody help me please

Thanks
JH