Hopefully an easy one, but I can't seem to do it.

How can I name my sheets with in a workbook to pick up a cell, i.e sheet 1
is call the same as cell A1.

Thanks

See

http://www.mcgimpsey.com/excel/event...efromcell.html


In article ,
Tom wrote:

Hopefully an easy one, but I can't seem to do it.

How can I name my sheets with in a workbook to pick up a cell, i.e sheet 1
is call the same as cell A1.

Thanks

This macro will go thru each worksheet and set its name equal to the value in
cell A1 in that sheet:

Sub namesheet()
For Each ws In Worksheets
ws.Activate
ActiveSheet.Name = Range("A1").Value
Next
End Sub

just make sure the names are both valid & unique
--
Gary''s Student - gsnu200747


"Tom" wrote:

Hopefully an easy one, but I can't seem to do it.

How can I name my sheets with in a workbook to pick up a cell, i.e sheet 1
is call the same as cell A1.

Thanks

JE provided you with the same procedure I would recommend. You may
also want want to incorporate a cleaner to get rid of any invalid
characters in A1. Something like:
Function cleanName(ByVal txtString As String)
invalidChrArray = Array(":", "/", "*", "\", "?")
For y = LBound(invalidChrArray) To UBound(invalidChrArray)
txtString = Replace(txtString, invalidChrArray(y), "", 1)
Next y
cleanName = txtString
End Function

Then you can clean Range A1 like cleanName(Range("A1").Text)
Tom wrote:
Hopefully an easy one, but I can't seem to do it.

How can I name my sheets with in a workbook to pick up a cell, i.e sheet 1
is call the same as cell A1.

Thanks

Thanks for your help everybody, all very useful and I got it working.

"JW" wrote:

JE provided you with the same procedure I would recommend. You may
also want want to incorporate a cleaner to get rid of any invalid
characters in A1. Something like:
Function cleanName(ByVal txtString As String)
invalidChrArray = Array(":", "/", "*", "\", "?")
For y = LBound(invalidChrArray) To UBound(invalidChrArray)
txtString = Replace(txtString, invalidChrArray(y), "", 1)
Next y
cleanName = txtString
End Function

Then you can clean Range A1 like cleanName(Range("A1").Text)
Tom wrote:
Hopefully an easy one, but I can't seem to do it.

How can I name my sheets with in a workbook to pick up a cell, i.e sheet 1
is call the same as cell A1.

Thanks