How can I copy a sheet with drop down boxes in Excel?

Question

I have a weird situation. I have a workbook that has 6 drop down boxes within one of the sheets. Everytime I copy the sheet within the same workbook I get a box that pops up that says:

A formula or sheet you want to move or copy contains the name "drop down name here", which already exists on the destination worksheet. Do you want to use this version of the name?

Then I just choose Yes, but I have to click Yes 6 times because that is how many drop downs I have. Is there any way around this?

Answer 1

I found this reguarding worksheets...

It should still apply to your dropdown problem though.

Try "localising" your names to worksheets that are going to be copied. So, if you have a worksheet called "Sheet1" and a name MyName that refers to range Sheet1!$A$1 then delete MyName and then redefine it as Sheet1!MyName refers to Sheet1!$A$1

Note the "Sheet1!" prefix to the definition of the Name.

You may still run into problems if you use data validation that refers to drop-down lists where the name of the list is localised to a different worksheet, or conditional formatting where the "Fomula Is" refers to a named range that is localised to another sheet.

Probably good practice to avoid duplicating global (not tied to sheet) names and local names. It can cause confusion over which name is being called.

Jan Karel Pieterse's "Name Manager" is a useful add-in tool for clearing out names that contain errors, identifying duplicate global/local names, localising names that you have already defined as global and do not want to go to the trouble of deleting first, and a whole host of other goodies