Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel Copy Error


(!)

kanfaar's Avatar
kanfaar kanfaar is offline
Computer Specs
Member with 158 posts.
THREAD STARTER
 
Join Date: Aug 2011
Experience: Avatar
25-Jul-2012, 12:53 PM #1
Excel Copy Error
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's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
25-Jul-2012, 10:20 PM #2
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's Avatar
kanfaar kanfaar is offline
Computer Specs
Member with 158 posts.
THREAD STARTER
 
Join Date: Aug 2011
Experience: Avatar
26-Jul-2012, 04:04 PM #3
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's Avatar
kanfaar kanfaar is offline
Computer Specs
Member with 158 posts.
THREAD STARTER
 
Join Date: Aug 2011
Experience: Avatar
30-Jul-2012, 11:33 AM #4
Bump...
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
30-Jul-2012, 09:22 PM #5
Maybe you can upload a sample of the book, so we can look into it
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
30-Jul-2012, 09:36 PM #6
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's Avatar
kanfaar kanfaar is offline
Computer Specs
Member with 158 posts.
THREAD STARTER
 
Join Date: Aug 2011
Experience: Avatar
30-Jul-2012, 09:52 PM #7
Nice! Thanks! I'll check it out and get back w/you.
kanfaar's Avatar
kanfaar kanfaar is offline
Computer Specs
Member with 158 posts.
THREAD STARTER
 
Join Date: Aug 2011
Experience: Avatar
31-Jul-2012, 01:48 PM #8
Hrrmm...each of those commands return a message: 1004 Application-defined or object-defined error.
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
31-Jul-2012, 09:30 PM #9
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's Avatar
Member with 4,693 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
01-Aug-2012, 07:49 AM #10
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
kanfaar's Avatar
kanfaar kanfaar is offline
Computer Specs
Member with 158 posts.
THREAD STARTER
 
Join Date: Aug 2011
Experience: Avatar
03-Aug-2012, 11:08 AM #11
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's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
05-Aug-2012, 09:46 PM #12
So how do you end up with MCxx when it doesn't exist in originating *.xls workbook?
kanfaar's Avatar
kanfaar kanfaar is offline
Computer Specs
Member with 158 posts.
THREAD STARTER
 
Join Date: Aug 2011
Experience: Avatar
05-Aug-2012, 10:01 PM #13
Hence my question here...
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
07-Aug-2012, 11:45 AM #14
looks like you have to upload your file here.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑