VBA creates named ranges, but named ranges disappear

Hi All

You've all been exceptionally helpful in the past and I hope to whatever gods there are that you can help me again....

I've created a spreadsheet for our Human Resources department to track how long it takes a new employment position to be filled, from start to finish.

I use data validation in the Tracking table for ease of use for the enduser, and I use named ranges to reference in the data validation.

I’m creating a macro that allows the user (at the click of a button) to create new tabs for the new year.

Almost everything works, but when I specify the named range to use for data validation (both in Excel and in VBA), the named ranges do not exist. To make it even more confusing (to me), after naming the ranges or running the macro (thus creating 2 new tabs), the named ranges exist only when selecting the last tab (named “Table” & year).

Because the named ranges don’t exist (apparently), I cannot use data validation with the new named ranges.

Why do the named ranges only show up when the tab in which they reside is selected?

I've included the code below and attached a sample worksheet.

Please Login or Register  to view this content.

Thanks in advance for the help!
BrotherNeptune

Thanks for the quick reply!

I've managed to change the scope from Worksheet to Workbook, but I'm running into further errors.... I'll play around a bit and see if I can find the solution.

I may be back but for now.... Thanks!