Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

In Microsoft Excel 2010, when I paste a range of cells that contain named ranges from one spreadsheet to another where the target also has the same named regions, I get the following dialog, once for each such replicated name range:

A formula or sheet you want to move or copy contains the name 'someNameHere', which already exists on the destination worksheet.

Do you want to use this version of the name?

  • To use the name as defined in the destination, click Yes.
  • To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box.

The default choice in the dialog is Yes.

I have a repetitive task where by I need to do this copy across about 60 sheets and each such paste has four instances of the dialog.

I always want the name as defined in the destination.

Is there anyway to configure Excel 2010 to temporarily suppress this dialog and always take the default action of using the name as defined in the destination without raising the dialog repeatedly?

share|improve this question
    
Don't think so. Would it acceptable to delete names from the source sheets, which can be done via VBA? Presumably, you wouldn't then save the "nameless" source sheets (in order to preserve the names). –  chuff Dec 30 '12 at 6:09
    
Would prefer not to use VBA due to the complexity that introduces. –  WilliamKF Dec 30 '12 at 17:01

1 Answer 1

up vote 1 down vote accepted

Whilst you have said that you don't want to use VBA, I believe that this is the best method to avoid the issue.

I would recommend creating a PERSONAL.XLSM workbook and create a macro in there that you assign to a shortcut or the ribbon.

As far as I can tell, the only other alternative is an external tool such as AutoHotKey or AutoIT. Both of these are free but do require some basic programming skills.

There are also a couple of tools that are designed to automatically respond to dialog boxes, these might be worth a look as well.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.