1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Copy Error

Discussion in 'Business Applications' started by kanfaar, Jul 25, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. kanfaar

    kanfaar Thread Starter

    Joined:
    Aug 18, 2011
    Messages:
    158
    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.
     
  2. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    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.
     
  3. kanfaar

    kanfaar Thread Starter

    Joined:
    Aug 18, 2011
    Messages:
    158
    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?
     
  4. kanfaar

    kanfaar Thread Starter

    Joined:
    Aug 18, 2011
    Messages:
    158
    Bump...
     
  5. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    Maybe you can upload a sample of the book, so we can look into it
     
  6. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    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.
     
  7. kanfaar

    kanfaar Thread Starter

    Joined:
    Aug 18, 2011
    Messages:
    158
    Nice! Thanks! I'll check it out and get back w/you.
     
  8. kanfaar

    kanfaar Thread Starter

    Joined:
    Aug 18, 2011
    Messages:
    158
    Hrrmm...each of those commands return a message: 1004 Application-defined or object-defined error.
     
  9. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    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.
     
  10. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    Try running the following macro on the workbook in question to find and delete any hidden references.

    Code:
    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
     
  11. kanfaar

    kanfaar Thread Starter

    Joined:
    Aug 18, 2011
    Messages:
    158
    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.
     
  12. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    So how do you end up with MCxx when it doesn't exist in originating *.xls workbook?
     
  13. kanfaar

    kanfaar Thread Starter

    Joined:
    Aug 18, 2011
    Messages:
    158
    Hence my question here...
     
  14. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    looks like you have to upload your file here.
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1062561