How can I avoid the clipboard message box when copying large amounts of data in Excel?

Question

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.

score 8 · Answer 1 · answered Jan 29 '12 at 18:50

8

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.

answered Jan 29 '12 at 18:50

Jon Peltier

4,392
22
26

1

I have this as an xlam addin that runs on the before close event – FreeSoftwareServers Sep 09 '19 at 05:15

score 5 · Accepted Answer · answered Jan 26 '12 at 19:18

5

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

answered Jan 26 '12 at 19:18

mischab1

1,312
8
10

score 4 · Answer 3 · edited Mar 20 '20 at 23:51

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 to set 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 have unknowingly turned off alerts until you quit Excel.

I tested his suggestion Application.CutCopyMode = False after the paste, it works fine. Here is an example 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

score 1 · Answer 4 · answered Jan 26 '12 at 17:41

1

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

answered Jan 26 '12 at 17:41

DaBaer

764
4
11

1

The article was very useful. Thanks very much. – RocketGoal Jan 27 '12 at 11:24
Link only answer :( – FreeSoftwareServers Sep 06 '19 at 08:37

How can I avoid the clipboard message box when copying large amounts of data in Excel?

4 Answers4

Linked