Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Links / March 2007

Tip: Looking for answers? Try searching our database.

Break Link cause Send Error Report

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Sara Hopkins - 12 Feb 2007 04:16 GMT
I have an Excel 2003 file with 4 tabs.  Sheet4 is a roster - dates and names.
The first of the date is sourced from Sheet1, the names on the roster are
sourced via data validation drop down boxes to dynamic named ranges on Sheet2
and Sheet3.  On Sheet1 is a button with a macro behind it that publishes the
roster - basically it copies Sheet4 to a blank workbook and breaks the links
to the original workbook.  Works beautifully in 2003.

However, when I try in on XP (2002) - the macro gives an Send Error Report
to MS error.  When I try to do the macro steps manually, it errors again when
I break the links (Edit | Links | Break link btn | Break Links btn).

There's no error message or number - just Send Error Report to Microsoft.

Has anyone seen this before?  Got a fix for it?

BTW it does the same thing if I save it down to Excel 2002 and then try it.
Bill Manville - 12 Feb 2007 07:36 GMT
Rather than using Break Links you could remove the links

On the copy of Sheet4 try

Sub CleanUp()  
 Dim N As Name
 Activesheet.Cells.Validation.Delete
 Activesheet.UsedRange.Copy
 Activesheet.UsedRange.PasteSpecial xlValues
 For Each N In ActiveWorkbook.Names
   N.Delete
 Next
End Sub
 
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup
Sara Hopkins - 25 Feb 2007 23:53 GMT
Thank you Bill, that worked, it leaves the sheet highlighted and in a "ready
to paste" mode, so I'm going to add in and ESC keystroke and a CTRL+HOME
keystroke.
Bill Manville - 05 Mar 2007 00:19 GMT
Rather than keystrokes I would recommend
Application.CutCopyMode=False
Range("A1").Select

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup
Sara Hopkins - 13 Mar 2007 02:36 GMT
Thanks again, Bill.  I tried that and got the following error:
Run-time error '1004'
Select method of Range class failed
Against the Range("A1").Select comment.
The error did not occur when I commented out that command.  It struck me
that I had the freeze panes on so I change A1 to A4 and tried again... no
luck.
I googled the error and OzGrid gave me a possible answer: change the line to
Sheets("sheet_name").Range("A4").Select
I did that and the macro now runs without error.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2010 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.