InputBox to name a range

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

In a worksheet named Title Generator I am trying to use a UserBox to name a
range then copy that range to a sheet named Sessions, per the code below.

This is producing appl. define or object defined error.

ActiveWorkbook.Names.Add Name:="SName", _
RefersTo:="=(Title Generator)$B$12:$T$503"

This is producing subscript out of range error.

ActiveWorkbook.Names.Add Name:="SName", _
RefersTo:="=Title Generator!$B$12:$T$503"

Option Explicit

Sub ToSessionSheet()
Dim SName As String
SName = InputBox("Enter a Session Name", "Session Name")
'MsgBox SName
If SName = vbNullString Then Exit Sub

ActiveWorkbook.Names.Add Name:="SName", _
RefersTo:="=(Title Generator)$B$12:$T$503"

Range(SName).Copy Sheets("Sessions").Range("B10000").End(xlUp).Offset(1, 0)
SName = vbNullString

End Sub

Thanks.
Howard
 
Hi Howard,

Am Mon, 25 Feb 2013 22:16:25 -0800 (PST) schrieb Howard:
ActiveWorkbook.Names.Add Name:="SName", _
RefersTo:="=(Title Generator)$B$12:$T$503"
Click to expand...

try:
ActiveWorkbook.Names.Add Name:=SName, _
RefersTo:=Sheets("Title Generator").Range("$B$12:$T$503")


Regards
Claus Busch
 
Hi Howard,



Am Mon, 25 Feb 2013 22:16:25 -0800 (PST) schrieb Howard:







try:

ActiveWorkbook.Names.Add Name:=SName, _

RefersTo:=Sheets("Title Generator").Range("$B$12:$T$503")





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Thanks Claus, I googled it and got close but could not make the final transition to work on my sheet and sheet names.

Appreciate it.

Regard,
Howard
 
Back
Top