Range Name Duplication and PasteSpecial Macro

[Guest]

I've got some VB code that takes information from workbook and pastes it
another as shown:

Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

The problem is that during pasting a messagebox comes up that says:

A formula or sheet you want to move or copy contains the name 'XXX', 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 sheet, 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.

XXX is a range name. Since there are about 30 named ranges in the
spreadsheet, the user has to click yes 30 times (once for each range name).
How can I avoid having the user do this?

 

[Tom Ogilvy]

Try
Application.DisplayAlerts = False
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.DisplayAlerts = True

 

[Guest]

I tried it and I'm still getting the message boxes. Any other ideas?

 

[Tom Ogilvy]

One method would be to remove the duplicate range names on the destination
sheet using code. (could affect existing formulas in the destination
workbook).

another would be to transfer the formulas as text strings

you would use the VBA equivalent to Edit=>Replace

Replace
what =
with ZZ=

then do you copy and pastspecial values. (rather than formulas)

then reverse it on the new sheet and the old sheet.

Replace
What ZZ=
with =

You would need to consider which names you want to be used.

--
Regards,
Tom Ogilvy

I tried it and I'm still getting the message boxes. Any other ideas?