Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a macro that is copying 4 sheets from one workbook to a new workbook and saving it down with another name.

Unfortunately the message box asking me whether I would like to make this large amount of data available for other applications is causing this automated job to become slightly manual.

Any tips on how to avoid the clipboard message box, or just have it automatically answer No, would be gratefully appreciated.

share|improve this question
up vote 4 down vote accepted

I would modify the macro. Turn off alerts when the macro starts and turn them back on when the macro is done. i.e. Application.DisplayAlerts = False

share|improve this answer
    
Thanks. In the end I used this. – RocketGoal Jan 27 '12 at 11:23

The problem with Application.DisplayAlerts = False is that it may hide an alert you need to see.

Use Application.CutCopyMode = False after the paste, which removes the link to the large range from the clipboard.

share|improve this answer

I found this MS document regarding this. It looks like you can use a few different programatic solutions in your macro to resolve this. It just depends on what exactly you are copying to clipboard.KB # 287392

share|improve this answer
    
The article was very useful. Thanks very much. – RocketGoal Jan 27 '12 at 11:24

I agree with Jon Peltier, "The problem with Application.DisplayAlerts = False is that it may hide an alert you need to see." Plus you have to remember "Application.DisplayAlerts = True" after you're done, so that's 3 lines of code for each paste AND if your program interrupts BEFORE "Application.DisplayAlerts = True", you'll unknowingly have alerts turned off until you quit Excel.

I tested his suggestion "Application.CutCopyMode = False after the paste", it works fine in someSpreadsheet.xls

Workbooks.OpenText Filename:="someDirectory\someFile.txt", _
    DataType:=xlDelimited, TextQualifier:=xlNone, _
    Tab:=True
Cells.Select
Selection.Copy
Windows("someSpreadsheet.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = 0
Windows("someFile.txt").Activate
ActiveWorkbook.Close
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .