Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copied sheet causing duplicate range name error (Excel 2010) | Excel Discussion (Misc queries) | |||
Paste copied data to specified sheet based on range - variable iss | Excel Programming | |||
When a sheet is copied, i want also that the Sub's whithin this sheet is copied to the new sheet | Excel Programming | |||
Get the print range from a sheet you just copied for use inthe new | Excel Programming | |||
Refer named range in a copied sheet | Excel Programming |