same named range on multiple worksheets?
 

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

same named range on multiple worksheets?
 

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Philip Reece-Heal" wrote in message ...
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

same named range on multiple worksheets?
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Philip Reece-Heal" wrote in message
...
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

same named range on multiple worksheets?
 

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
"Ron de Bruin" wrote in message
...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Philip Reece-Heal" wrote in message
...
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

same named range on multiple worksheets?
 

THIS WORKS GREAT! Thank you!

"Ron de Bruin" wrote:

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Philip Reece-Heal" wrote in message ...
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