Hi all

Having copied a range of cells to the clipboard, I wish to paste the range into another area but with the following complications:

The destination range is contained in another (open) workbook.

I only want to paste the formulae, not formats or validation etc.

The copied cells include references to defined "names" which are identical in both the source and destination worksheets. If I do it manually then a dialog box pops up asking if I wish to use the same name, which I accept, but the trouble is that it does this individually for each instance that it encounters and there seems to be no global instruction to say "yes, do it for this instance and for every other occasion". There can be hundreds of these and I do not want to have to answer the prompt a hundred times.

I want the formulae in the destination NOT to refer to the source workbook under any circumstance. However, where the formula in a particular cell refers to a different worksheet in the source workbook, then the act of "pastespecial formulae" results in the destination cell calling the source workbook, ie
Original cell D3, in worksheet "Sheet1" of the workbook C:\MyWork\SourceBook, is one of the cells in the range to be copied, and it contains the formula
=Sheet2!D3+1
If I copy this and pasteSpecial Formulae to cell D3 in the destination workbook, it comes out as
='[C:\MyWork\SourceBook]Sheet2'!D3+1
where the desired result is
=Sheet2!D3+1
Ie it needs to refer to Sheet2 of the destination workbook, not Sheet2 of the source workbook.

I sometimes make use of Bill Manville's excellent FindLink utility but it is not quite up to the job for this task, because it will replace the formulae with a hardcoded value when deleting the link. Quite understandable, of course.

Anyway, I think that am going to need a VBA solution to the very particular requirements here and would be grateful to receive suggestions.

Thanks in advance for any help