same named range on multiple worksheets?

[Philip Reece-Heal]

I have a acquired a workbook with many worksheets and I want to apply the
same name to the same cell on each worksheet.
To date, the only way I have managed this is to name the cell in the first
worksheet, then for each of the remaining sheets, move the sheet to a new
workbook, name the cell and then move the worksheet back into the original
workbook. Slow and laborious.

Anyone know of a quicker method?

Any suggestions gratefully received

Philip

 

[Ron de Bruin]

Try this macro to do this

Sub Give_name_on_all_sheets()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
Sheets(Sh.Name).Range("A5").Name = Sh.Name & "!yourname"
Next
End Sub

or do it manual

 

[Bob Phillips]

Select each sheet in turn, select the cell(s), Insert>Name>Define... and add
the name of 'sheetname'!name, and Add.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Philip Reece-Heal]

Thanks Ron
That macro works a treat. A real timesaver

Thanks also to you Bob. Your method is certainly quicker than my old way

Regards
Philip

 

[ruddojo]

THIS WORKS GREAT! Thank you!

Try this macro to do this

Sub Give_name_on_all_sheets()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
Sheets(Sh.Name).Range("A5").Name = Sh.Name & "!yourname"
Next
End Sub

or do it manual