V.B selecting ranges for naming
 

HI, i have a problem with a worksheet_change event that i am trying t
put together and was wondering if anyone could help me.
This is what i have at the moment.

VB:
--------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

' Hide operations from the user
Application.ScreenUpdating = False
Sheets("workers").Select
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="worker_code", RefersToR1C1:= _
"=Workers!R3C4:R14C4"
Range("D2").Select
--------------------------------------------------------------------------------

what i want it to do is rename a range of cells with data in them.
But i dont know how to make the line which says

VB:
--------------------------------------------------------------------------------
ActiveWorkbook.Names.Add Name:="worker_code", RefersToR1C1:= _
"=Workers!R3C4:R14C4"
--------------------------------------------------------------------------------

do the same procedure as while re-naming that range.

VB:
--------------------------------------------------------------------------------
Range(Selection, Selection.End(xlDown)).Select
--------------------------------------------------------------------------------

I hope i have given you enough information to help you understand m
problem. Please any help would be greatly appreciated

--
Message posted from http://www.ExcelForum.com

V.B selecting ranges for naming
 

Is this what you mean


Sheets("workers").Range("D3",Range("D3").End(xlDow n)).Name:="worker_code
Range("D2").Select

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ice_cool " wrote in message
...
HI, i have a problem with a worksheet_change event that i am trying to
put together and was wondering if anyone could help me.
This is what i have at the moment.

VB:
--------------------------------------------------------------------------
------
Private Sub Worksheet_Change(ByVal Target As Range)

' Hide operations from the user
Application.ScreenUpdating = False
Sheets("workers").Select
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="worker_code", RefersToR1C1:= _
"=Workers!R3C4:R14C4"
Range("D2").Select
--------------------------------------------------------------------------
------

what i want it to do is rename a range of cells with data in them.
But i dont know how to make the line which says

VB:
--------------------------------------------------------------------------
------
ActiveWorkbook.Names.Add Name:="worker_code", RefersToR1C1:= _
"=Workers!R3C4:R14C4"
--------------------------------------------------------------------------
------

do the same procedure as while re-naming that range.

VB:
--------------------------------------------------------------------------
------
Range(Selection, Selection.End(xlDown)).Select
--------------------------------------------------------------------------
------

I hope i have given you enough information to help you understand my
problem. Please any help would be greatly appreciated.


---
Message posted from http://www.ExcelForum.com/

V.B selecting ranges for naming
 

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("workers")
.Range(.Range("D3"), _
.Range("D3").End(xlDown)).Name _
= "worker_code"
End With
End Sub

works for me and is hidden from the user. Or to use Bob's approach

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Sheets("workers").Select
Sheets("workers").Range("D3", _
Sheets("workers").Range("D3") _
.End(xlDown)).Name = "worker_code"
Sheets("workers").Range("D2").Select
Application.ScreenUpdating = True
End Sub

You need to qualify entries on the active sheet since this is in the sheet
code of a different sheet.

--
Regards,
Tom Ogilvy

"ice_cool " wrote in message
...
HI, i have a problem with a worksheet_change event that i am trying to
put together and was wondering if anyone could help me.
This is what i have at the moment.

VB:
--------------------------------------------------------------------------
------
Private Sub Worksheet_Change(ByVal Target As Range)

' Hide operations from the user
Application.ScreenUpdating = False
Sheets("workers").Select
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="worker_code", RefersToR1C1:= _
"=Workers!R3C4:R14C4"
Range("D2").Select
--------------------------------------------------------------------------
------

what i want it to do is rename a range of cells with data in them.
But i dont know how to make the line which says

VB:
--------------------------------------------------------------------------
------
ActiveWorkbook.Names.Add Name:="worker_code", RefersToR1C1:= _
"=Workers!R3C4:R14C4"
--------------------------------------------------------------------------
------

do the same procedure as while re-naming that range.

VB:
--------------------------------------------------------------------------
------
Range(Selection, Selection.End(xlDown)).Select
--------------------------------------------------------------------------
------

I hope i have given you enough information to help you understand my
problem. Please any help would be greatly appreciated.


---
Message posted from http://www.ExcelForum.com/