Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

4 Answers 4

up vote 3 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.