Named Range Macro
 

My code is as follows:

a = InputBox("Enter sheet name exactly as labeled:")
Range("E6").Select
ActiveCell = a
Sheets(a).Select

If Err.Number = 9 Then
MsgBox "Sorry but your desired sheet does not exist in this workbook,
please verify sheet name and run analysis again.", vbExclamation, "Error!"
End If

Range("EF25").Select
ActiveWorkbook.Names.Add Name:="defective_rate",
RefersToR1C1:="=Vendor!R25C136"


----The only change I need to make is instead of declaring the named range
to RefersToR1C1:="=Vendor!R25C136, I would like the named range to refer to
R25C136 on the sheet that the user indicates (listed in the code as a)- how
do I do this???? Thanks!

Named Range Macro
 

A bit simpler, perhaps
Sub createname()
On Error GoTo nosheet
a = InputBox("Enter sheet name")
Sheets(a).Cells(25, 136).Name = "defective_rate"
Exit Sub
nosheet: MsgBox "No such sheet, try again"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Lost" wrote in message
...
My code is as follows:

a = InputBox("Enter sheet name exactly as labeled:")
Range("E6").Select
ActiveCell = a
Sheets(a).Select

If Err.Number = 9 Then
MsgBox "Sorry but your desired sheet does not exist in this workbook,
please verify sheet name and run analysis again.", vbExclamation, "Error!"
End If

Range("EF25").Select
ActiveWorkbook.Names.Add Name:="defective_rate",
RefersToR1C1:="=Vendor!R25C136"


----The only change I need to make is instead of declaring the named range
to RefersToR1C1:="=Vendor!R25C136, I would like the named range to refer
to
R25C136 on the sheet that the user indicates (listed in the code as a)-
how
do I do this???? Thanks!