Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. 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.
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:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. 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: 
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:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10696) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Clearing Large Clipboard Entries.
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!
Press Enter when working in a workbook and Excel moves to a cell adjacent to the one in which you were working. If you ...
Discover MoreExcel allows you to define the data in a worksheet as a table. Doing so can provide some clear benefits over simply ...
Discover MoreOne of the settings you can make in Excel is to specify a user's name. This name is accessible through macros, and can ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-02-25 03:38:44
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
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??
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments