Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Clearing Large Clipboard Entries.

Clearing Large Clipboard Entries

by Allen Wyatt
(last updated February 22, 2019)

6

Have you ever noticed that after you've copied a large amount of data to the Clipboard, and then you close Excel, you see a dialog box stating that "A large amount of data is present in the Clipboard. Do you want to save it before quitting?" You may get tired of seeing this message. If so, then there are a couple of things you can do.

First, you can replace the "large amount" with a "small amount." (Excel never asks if you want to discard a small amount.) To do this, just select a single cell in your worksheet and copy it to the Clipboard. The contents of the single cell replace the large amount of data on the Clipboard, and you can exit Excel without seeing the message.

If you'd rather have a macro approach, you can do the exact same thing in a macro—just select cell A1 and have your macro copy it to the Clipboard:

Sub GoAway1()
    ActiveSheet.Range("A1").Copy
End Sub

Another approach is to use a single-line macro that basically "disables" the Clipboard by canceling any current copy operation:

Sub GoAway2()
    Application.CutCopyMode = False
End Sub

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10696) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Clearing Large Clipboard Entries.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Searching for Borders

Want to find all the paragraphs in your document that have borders applied to them? The regular Find and Replace tool ...

Discover More

Extracting a State and a ZIP Code

Excel is often used to process or edit data in some way. For example, you may have a bunch of addresses from which you ...

Discover More

Excel Custom Formats

The fundamental building block to displaying your data is the feature known as custom formats. Here is the definitive ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Speeding Up Large Worksheets

If your worksheet gets large enough, you may notice a severe slowdown when it is recalculated. This tip provides some ...

Discover More

A Shortcut for Switching Focus

While not technically an Excel-only tip, the shortcuts described in this tip will help you switch focus from your ...

Discover More

Disabling a Function Key

Function keys are used to perform common tasks in Excel. If you want to disable one of the function keys, it's rather ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine more than 0?

2019-02-25 03:38:44

Alan Elston

Thanks Allen, I did not know about that. I see there is some info on it here:
H t t p s : / / windows.tips.net/T012951_M8_Free_Clipboard.html
I will take a look at that. ( I am interested in examining specifically what is in each clipboard as I might want to try to control exactly what versions I get from where after I copy to clipboard. I will take a look and see if M8 Free Clipboard is any help in that

Alan Elston


2019-02-24 16:13:35

Allan

Then there is the mini program "M8 Free Clipboard" in which all of your clipboards are added and kept. You can access it easily from the Task Bar, select a desired clip and past it directly into a document or delete it. I use it daily. Great program!


2019-02-23 03:40:48

Alan Elston

It may be useful to note that often there will be a few Clipboards in Play, ( which themselves may hold different format versions of what you copy )
The Blog from Allen Wyatt here is talking about the Excel Clipboard
There is also a Windows Clipboard. This can be cleared with a small bit of programming.
Then there is an Office Clipboard, which is what you can usually see in Excel 2007 + by clicking the arrow in the lower right corner of the Clipboard group of the Home tab of the ribbon. (Office Clipboard.JPG : h t t p s : / / imgur.com/DA1o5OT ). That needs a very big code to clear that programmatically. If anyone needs that , then let me know, - it is a bit too big to post here
Alan Elston

(see Figure 1 below)

Figure 1. 


2019-02-22 13:56:11

Allan

There is also a shortcut to clear the Clipboard that can be added to the Task Bar

Clipboard, Clear Windows 7
1. Right-click on your desktop, and select New>Shortcut.
2. Copy and paste the following command into the shortcut:cmd /c “echo off. | clip”
3. Choose Next.
4. Enter a name for this shortcut such as Clear My Clipboard.
5. Double-click the shortcut anytime you want to clear your clipboard.


2019-02-22 09:00:45

Alan Cannon

Why not use an event driven macro using Before_Close to automatically copy the cell to the clipboard. You could even specify a cell that is always blank, such as the last cell of the worksheet.


2017-11-16 08:46:07

askahy

hi the message doesnt show up but did you notice there is a big amount of change in the size of the workbook ? i mean, i have two workbooks, one working fine and the other showing the dialog box of large amount of data... the one working fine (still the message is prompting though) which consists of 18 pages and 3 sheets and the other (which is my concern) have only 4 pages and 3 sheets but the size increases a lot when i copy one page to make a new page below it. (increase - im talking about is almost 35-40 KB) where as in the first one if i do copy and pasteing it increases only by 2 KB (content to copy in both workbooks are almost same) so that increase makes the workbook open late and it lags a lot. any suggestion??


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.