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

Using Document Properties to Ensure Consistent References

If you need to refer to the same information over and over in a document, you may be interested in using custom document ...

Discover More

Stopping Styles from Changing with Multiple Users

Using styles in your documents can be very helpful when it comes to consistency and ease of formatting. When others open ...

Discover More

Word 2013 Terrific Tables (Table of Contents)

Word allows you to create and format tabular information using a powerful table editor. Discover the many ways that you ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Weird Mouse Shortcut

If you like to use the mouse in your worksheet navigation efforts, you'll want to pay attention to this tip. Here you ...

Discover More

Disabling the F1 Key

Tired of hitting the F1 key by mistake and pulling up the Help system? Here are a couple of ways (one drastic and one not ...

Discover More

Making Pane Settings Persist

When you freeze panes in a worksheet, those panes should persist even though you save the workbook and reload it. There ...

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 four less than 4?

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.