Named Range Macro

L

Lost

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!
 
D

Don Guillett

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top