[kanfaar]

Hello folks,

I'm attempting to copy a sheet from one workbook to another and am receiving approx. 20 errors like the following:

"a formula you want to move of copy contains the name MC10 which conflicts with a valid range reference of a name used internally"

The only difference between all of the errors I'm receiving is the numbers after "MC"; so, essentially, the error is more like "...contains the name MC## which conflicts..."

I would like to locate this "MC##" but have been unsuccessful in all attempts. I've tried searching the entire workbooks (both the source and destination workbooks) for any instance of this, um, word; nothing found. I've even searched through the Name Manager (in both workbooks) to no avail. I even tried script-based solutions like: "on error resume next"

So far, nothing other than manual intervention on my part (eg - clicking the "Yes" button on the 20-ish error windows) so that Excel will actually copy the page.

Anybody have any ideas? This error is making my macro unable to copy this one sheet and it's the last one before printing, so now my report is never complete after the first run-through.

[Garf13LD]

This means your original sheet has a named range called "MC10".
Worksheets before excel 2007 only has 256 columns (A-IV), so using MC10 is valid.
Since excel 2007, the columns count increase to 16,367, so MC10 is a cell reference like A1,B5,....
Change the name in your original sheet before you copy to new sheets.

[kanfaar]

Yep, I understand what the error is /supposed/ to mean, but as stated previously, I have searched for any reference to MC10 in all formulae, in the cell MC10 (which is empty), and in the Name Manager. I have been unable to locate such, or I would change it. Is there somewhere I'm missing to look?

[kanfaar]

Bump...

[Garf13LD]

Maybe you can upload a sample of the book, so we can look into it

[Garf13LD]

On 2nd though, there might be hidden names in your book.
press alt+f11 to go into vbe, and in the immediate window type
msgbox activeworkbook.names("MC10").refersto
or
msgbox activesheet.range("MC10").address
or
msgbox activesheet.range("MC10").parent.name

1st one checks if name "MC10" exists and what it refers to
2nd one checks the address of "MC10". If it refers to a name, its address will NOT be $MC$10.
Last to get the sheet it belongs to.

If you can't see immediate window in vbe, use ctrl+G to display it.

[kanfaar]

Nice! Thanks! I'll check it out and get back w/you.

[kanfaar]

Hrrmm...each of those commands return a message: 1004 Application-defined or object-defined error.

[Garf13LD]

what is the extension or format of your file?
if only .xls, then the file is in old format and the reference is beyond the sheet.

[Rollin_Again]

Try running the following macro on the workbook in question to find and delete any hidden references.

Sub DeleteReferences()

For Each vRef In ActiveWorkbook.Names
If MsgBox("Delete Reference " & vRef.Name & "?" & vbCrLf & vRef.RefersTo, vbYesNo) = vbYes Then
vRef.Delete
End If
Next vRef

End Sub

Rollin

[kanfaar]

Hey Garf13LD: Yep, the originating workbook is an *.xls and I realize that the MC10 reference is outside of its range. The one I'm copying to is an *.xlsm and it would be /within/ the range. However, there is no reference that I can find to "MC10". I search for "MC10" and do not find it; please stop telling me what the error message is supposed to mean. I understand that part of it.

Rollin_Again: I ran your code there and didn't notice anything with "MC10" in it. There were a lot of print ranges and some of them have "MC" in them, but that's a sheet name and they were always followed by "!"...so the reference read like: "MC!$A$1:$H$63". Nothing with the elusive "MC10", though.

[Garf13LD]

So how do you end up with MCxx when it doesn't exist in originating *.xls workbook?

[kanfaar]

Hence my question here...

[Garf13LD]

looks like you have to upload your file here.