vba - Renaming a sheet based of specific words in a cell -

i want make macro take value in cell , rename sheet using value. want part cell value used sheet name.

for example, in cell a1, says "john doe"... want macro name sheet "doe, j.". how that?

i know how name sheet based off entire cell.

sub renamesheet()         dim rs worksheet     each rs in sheets         rs.name = rs.range("b5")     next rs     end sub 

you need parse string name first, , hence check if sheet exists (if so, adding counter , make sure don't name 2 sheets same name). this:

sub renamesheet()  'variables declaration dim rs worksheet dim new_name string, tmp_new_name string dim counter integer: counter = 0 dim counter1 integer: counter1 = 1 dim allnames object  'code set allnames = createobject("scripting.dictionary")  each rs in sheets     'first, let's parse name "last name" + ", " + "name initial" + "."       new_name = split(rs.range("b5"), " ")(1) + ", " + left(split(rs.range("b5"), " ")(0), 1) + "."     'check if exists     if allnames.exists(new_name)         'add counter "(n)" until doesn't exist anymore         tmp_new_name = new_name         while allnames.exists(tmp_new_name) <> false             tmp_new_name = new_name & " (" & counter1 & ")"             counter1 = counter1 + 1         loop         counter1 = 1         new_name = tmp_new_name     end if     'rename     rs.name = new_name     counter = counter + 1     'keep name stored in memory (into dictionary)     allnames.add rs.name, counter next rs end sub