Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Name the range AFTER copied to new sheet

I wrote this code and it works fine, EXCEPT now I see that I need to assign the 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 Title Generator, 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).O ffset(1, 0).PasteSpecial Paste:=xlPasteValues

SName = vbNullString
End Sub

Thanks,
Howard
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Name the range AFTER copied to new sheet

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


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Name the range AFTER copied to new sheet

On Tuesday, February 26, 2013 1:34:12 PM UTC-8, GS wrote:
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


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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Name the range AFTER copied to new sheet

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


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default Name the range AFTER copied to new sheet

On Tuesday, February 26, 2013 3:35:32 PM UTC-8, GS wrote:
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


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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Name the range AFTER copied to new sheet

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


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


  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Name the range AFTER copied to new sheet

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!


--
Garry

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copied sheet causing duplicate range name error (Excel 2010) jgeniti Excel Discussion (Misc queries) 0 November 17th 11 04:55 PM
Paste copied data to specified sheet based on range - variable iss fishy Excel Programming 6 April 1st 10 02:00 PM
When a sheet is copied, i want also that the Sub's whithin this sheet is copied to the new sheet Luc[_8_] Excel Programming 2 January 12th 10 05:49 AM
Get the print range from a sheet you just copied for use inthe new pickytweety Excel Programming 2 April 25th 09 06:01 AM
Refer named range in a copied sheet Sajit Excel Programming 2 November 30th 07 04:45 PM


All times are GMT +1. The time now is 02:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"