Dim rng as Range, cell as Range
set rng = selection
for each cell in selection
worksheets.Add After:=Worksheets(worksheets.count)
ActiveSheet.Name = Left(Cell.value,31)
Next

There is no reason to add an additional check if the length is greater than
31. If it is less than or equal 31, you get the original value and if more
than 31, you get the left 31 characters. Demo from the immediate window:

? len(Left("ABCD",31))
4

--
Regards,
Tom Ogilvy

"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