Name the range AFTER copied to new sheet

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

Howard

I wrote this code and it works fine, EXCEPT now I see that I need to assignthe SName to the range I copied to the Sessions sheet AFTER it has been copied to the new sheet. The way I have it now is the name refers to the range on the Title Generator sheet and I need it to refer to the 'just copied' range the in Sessions sheet.

I will copy the same range several times from Title Generator to Sessions, each time with different data and a different name.

Later I will have a need to recall those Session named ranges back to TitleGenerator, so I will need to refer to the names in Sessions.

I'm unsure how to name the range after it has been copied...?


Sub ToSessionsSheet()
Dim SName As String

SName = InputBox("Enter a name for this Session", "Session Namer")
If SName = vbNullString Then Exit Sub

'Adds session names to the drop down list used for cell A9 drop down
Range("AD30").End(xlUp).Offset(1, 0) = SName

'Assigns the session name to the range B11:T513
ActiveWorkbook.Names.Add Name:=SName, _
RefersTo:=Sheets("Title Generator").Range("$B$11").Resize(503, 19)

'copies to sheet Sessions
Range("$B$11").Resize(503, 19).Copy
Worksheets("Sessions").Range("B10000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

SName = vbNullString
End Sub

Thanks,
Howard
 
And so you have a perfect example of why you should never use global
scope with defined names unless it's *absolutely necessary*! If you
want names to be reusable on more than 1 sheet then they *must* be
defined with local scope as follows...

'Sheetname'!DefinedName

...where the sheetname is wrapped in apostrophes, and delimited from the
defined name by the exclamation character. Doing this will allow you to
reuse the name on as many sheets as desired. Note that the RefersTo
does not, however, need to be the same address on every sheet using the
name.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
And so you have a perfect example of why you should never use global

scope with defined names unless it's *absolutely necessary*! If you

want names to be reusable on more than 1 sheet then they *must* be

defined with local scope as follows...



'Sheetname'!DefinedName



..where the sheetname is wrapped in apostrophes, and delimited from the

defined name by the exclamation character. Doing this will allow you to

reuse the name on as many sheets as desired. Note that the RefersTo

does not, however, need to be the same address on every sheet using the

name.



HTH



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion
Click to expand...

Thanks Garry for taking a look.

You offer some powerful information if I could get my head around it. I'm afraid I am out of the fry pan into the fire.

If I take this 'Sheetname'!DefinedName and do this 'Sessions'!sname it does not compile, at least as it sits by itself, sname should be SName right?

Frankly, I'm at a loss as to how to incorporate 'Sheetname'!DefinedName into my range naming code line.

Howard
 
Example...

Sub AddDefinedName(ByVal sName$, sRefersTo$, _
Optional IsLocal As Boolean = True)
Dim oScope As Object
If IsLocal Then
oScope = ActiveSheet: sName = "'" & oScope.Name & "'!" & sName
Else
oScope = ActiveWorkbook
End If ÌsLocal
oScope.Names.Add sName, RefersTo:= sRefersTo
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Example...



Sub AddDefinedName(ByVal sName$, sRefersTo$, _

Optional IsLocal As Boolean = True)

Dim oScope As Object

If IsLocal Then

oScope = ActiveSheet: sName = "'" & oScope.Name & "'!" & sName

Else

oScope = ActiveWorkbook

End If ï¿œsLocal

oScope.Names.Add sName, RefersTo:= sRefersTo

End Sub



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion
Click to expand...

Garry,
I pasted your example and this line errored out in red:

End If ï¿œsLocal

Even had it not errored it's beyond my pay grade of understanding.

I'll probably just have to go to the copy-to sheet and select and name themmanually.

I do appreciate your time trying to beat it into my thick skull.

Thanks,
Howard
 
Howard presented the following explanation :
Garry,
I pasted your example and this line errored out in red:

End If �sLocal

Even had it not errored it's beyond my pay grade of understanding.

I'll probably just have to go to the copy-to sheet and select and name them
manually.

I do appreciate your time trying to beat it into my thick skull.

Thanks,
Howard
Click to expand...

Sorry but I didn't not the keyboard went for a nap. It happens after
apps sit idle and I can't seem to pinpoint the cause. The line should
read...

End If 'IsLocal

...as I tend to add the criteria as a comment so I know which structure
has ended. My apologies for not catching this before I posted!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Another typo...Sorry but I didn't notice the keyboard went for a nap. It happens
after apps sit
idle and I can't seem to pinpoint the cause. The line should read...

End If 'IsLocal

..as I tend to add the criteria as a comment so I know which structure has
ended. My apologies for not catching this before I posted!
Click to expand...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top