Naming multiple sheets in a workbook using cells within the workbo

[Guest]

I have tried various codes given out on this site already with no luck. I am
able to change the name using the most basic of code and assigning a value,
however I need the worksheets to pull from multiple cells in a worksheet.
Basically mirroring the name of the data within. I would prefer the most
basic of codes as I am not familiar with this. Can anyone help? thanks

 

[Tom Ogilvy]

do you mean on one sheet you have a list of names and you want to name the
sheets using this list?

You will find the most basic of clearly stated explanations will often be
the most productive.

Assume the above - a list of names on worksheet Sheet1 in A1 to A10 (10
sheets in the workbook)

Sub NameSheets()
Dim i as Long, cell as Range
i = 0
for each cell in worksheets("Sheet1").Range("A1:A10")
i = i + 1
worksheets(i).Name = cell.Value
Next
End Sub


This further assumes that you don't already have sheets in other positions
with these names thus creating a possible duplicate name situation.

the other pssible assumption is that you have the intended name of the sheet
in cell A1 of each sheet

Sub NameSheets()
for each sh in Worksheets
sh.Name = sh.Range("A1").Value
Next
end Sub

or for multiple cells (assume A1 and C3

Sub NameSheets()
for each sh in Worksheets
sh.Name = sh.Range("A1").Value & sh.Range("C3").Value
Next
end Sub

 

[Guest]

thanks Tom. Tried the multiple cell one and got an error (400?). The
problem with using the same worksheet data is that the sheets pull from a
main data sheet within the workbook. Is there a way to pull another
worksheet named Event Data Form?

 

[Tom Ogilvy]

Probably but what is on Event Data Form and how would it be excluded from
the renaming - how would specific names be associated with specific sheet -
or doesn't it matter. Generally a 400 error (a small message box with
circle X and a 400? - you might try rebooting and trying again) isn't
caused by an error in the code.

 

[Guest]

thanks.

still getting the error. Event Data Form is the raw data set - includes
name of event and location (region and county). The naming convention I am
trying to pull in refers to the location and name of these events. I will
reboot and retry. thanks for your help.

 

[Tom Ogilvy]

Continuing to guess:

Assume in sheet Event Data Form, the location in is in A1:A10 and the
corresponding name is in B1:B10. Assume 11 sheets in the workbook (one
named Event Data Form which will not be renamed).

Sub NameSheets()
Dim i as Long, cell as Range
i = 0
for each cell in worksheets("Event Data Form").Range("A1:A10")
if lcase(worksheets(i + 1).Name) = "event data form" then
i = i + 2
else
i = i + 1
end if
worksheets(i).Name = cell.Value & "_" & cell.offset(0,1).Value
Next
End Sub