How to change a validation range from Sheet1 to Sheet2

I'm giving users the option of creating a new worksheet in a workbook i am creating.  I've hit an unfortunate snag and can't quite figure out how to do it.  Some help with this would absolutely appreciated.

The Data validation range in Line 2 refers to a static sheet name.  Is there a way to make this sheet name dynamic so that i can create new sheets with this code attached?

Dave if your out there, this ones right up your alley.
If myStrRngofOptions <> "" Then
                    Application.Names.Add Name:="TempRange", RefersTo:="='Estimating Template'!$BB$2:INDEX('Estimating Template'!$BB:$BB,MATCH(REPT(""Z"",20),'Estimating Template'!$BB:$BB))"
                    Set tmpRng = Range("TempRange").EntireColumn
                    tmpRng.Clear
                    tmpRng.Cells(1, 1).Value = "TempRange"
                    tmpRng.Cells(2, 1).Resize(UBound(myRngofOptions) + 1, 1).Value = Application.Transpose(myRngofOptions)
                    Cells(Rows.Count, tmpRng.Column).End(xlUp).Offset(1, 0).Value = " " 'do deal with 1 row ranges and the dynamic TempRange formula
                    'ok - now load the unique range
                    Call loadUniqueValidationList(Target, ActiveSheet, "TempRange")

Open in new window

DadXSixAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
Change:

    Application.Names.Add Name:="TempRange", RefersTo:="='Estimating Template'!$BB$2:INDEX('Estimating Template'!$BB:$BB,MATCH(REPT(""Z"",20),'Estimating Template'!$BB:$BB))"

To:

    Application.Names.Add Name:="TempRange", RefersTo:="='" & WorksheetName & "'!$BB$2:INDEX('" & WorksheetName & "'!$BB:$BB,MATCH(REPT(""Z"",20),'" & WorksheetName & "'!$BB:$BB))"

Kevin
zorvek (Kevin Jones)ConsultantCommented:
Or, if the target worksheet is the active worksheet:

    Application.Names.Add Name:="TempRange", RefersTo:="='" & ActiveSheet.Name & "'!$BB$2:INDEX('" & ActiveSheet.Name & "'!$BB:$BB,MATCH(REPT(""Z"",20),'" & ActiveSheet.Name & "'!$BB:$BB))"

Kevin
DadXSixAuthor Commented:
Kevin,

Your submitted code, yielded another error.  Attached is a copy of the workbook im attempting this in, so you can get a better idea of what im attempting.  Would it be possible to point it to another sheet that will remain static?

The Worksheet 'Material Database' will be static, however it will also be hidden, if that plays a role in it.
TestingCopy.xls
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dlmilleCommented:
We have to scope the TempRange if its not the active sheet.  Note the explict reference to the sheet Admin where I put the Temporary Range for the drop down list feed.

 
'now set the data validation range, following the same approach, as above - create a temporary range to an existing defined name, then use the load unique function
            If myStrRngofOptions <> "" Then
                    Application.Names.Add Name:="TempRange", RefersTo:="='Admin'!$BB$2:INDEX('Admin'!$BB:$BB,MATCH(REPT(""Z"",20),'Admin'!$BB:$BB))"
                    Sheets("Admin").Range("BB1").Value = "TempRange"
                    Set tmpRng = Sheets("Admin").Range("TempRange").EntireColumn
                    tmpRng.Clear
                    tmpRng.Cells(1, 1).Value = "TempRange"
                    tmpRng.Cells(2, 1).Resize(UBound(myRngofOptions) + 1, 1).Value = Application.Transpose(myRngofOptions)
                    Sheets("Admin").Cells(Rows.Count, tmpRng.Column).End(xlUp).Offset(1, 0).Value = "   " 'to deal with 1 row ranges and the dynamic TempRange formula
                    'ok - now load the unique range
                    Call loadUniqueValidationList(Target, Sheets("Admin"), "TempRange")

Open in new window


I first deleted TempRange using the name manager, then implemented the above code.

I've tested and everything seems to work well.  If you choose to move the temporary range to another sheet, the references to Admin would be changed.

It does not matter if the reference sheet that you put this in is hidden, as long as its not protected and the column BB is open for this temporary range.
Enjoy!

Dave
TestingCopy-r2.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DadXSixAuthor Commented:
Dave,
Is see the lines of code that you added.  So line 1 below tells us where the TempRange name points to and line 2 assigns the variable tmpRng to that location?
Sheets("Admin").Range("BB1").Value = "TempRange"
Set tmpRng = Sheets("Admin").Range("TempRange").EntireColumn

Open in new window

dlmilleCommented:
Sorry - its a bit convoluted.  TempRange is a dynamic range that returns from the 2nd row to the end of whatever is populated there.  I put a label on the range just as an FYI with the assignment in line 1 - and if that column is empty, the assignment to tmpRng would flag an error - so by putting data there, I ensure I never get an error.  

Subsequent lines then use tmpRng which points to the entire column of the dynamic array TempRange.  I could have just set tmpRng to the entire column of BB:BB as easily.  What's important is that the list of possible selection items are populated from row 2 down and then are made unique with the loadUniqueValidationList() routine.

Dave
DadXSixAuthor Commented:
Dave,

Once again i thank you.  Your answers are concise and well written and very much appreciated!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.