Disable clipboard prompt in Excel VBA on workbook close

Question

I have an Excel workbook, which using VBA code that opens another workbook, copies some data into the original, then closes the second workbook.

When I close the second workbook (using Application.Close), I get a prompt for:

Do you want to save the clipboard.

Is there a command in VBA which will bypass this prompt?

This I learned the hard way: Avoid using the clipboard if at all possible! Other programs may read from / write to the clipboard while your code is running, which will cause wild, unpredictable results. Note that Copy-Paste uses the clipboard (unsafe), while direct Copying as in chris neilsen's answer does not (and is therefore safe). — Jean-François Corbett, Mar 03 '11 at 12:08

score 46 · Accepted Answer · edited May 18 '15 at 13:08

46

I can offer two options

Direct copy

Based on your description I'm guessing you are doing something like

Set wb2 = Application.Workbooks.Open("YourFile.xls")
wb2.Sheets("YourSheet").[<YourRange>].Copy
ThisWorkbook.Sheets("SomeSheet").Paste
wb2.close

If this is the case, you don't need to copy via the clipboard. This method copies from source to destination directly. No data in clipboard = no prompt

Set wb2 = Application.Workbooks.Open("YourFile.xls")
wb2.Sheets("YourSheet").[<YourRange>].Copy ThisWorkbook.Sheets("SomeSheet").Cells(<YourCell")
wb2.close

Suppress prompt

You can prevent all alert pop-ups by setting

Application.DisplayAlerts = False

[Edit]

To copy values only: don't use copy/paste at all

Dim rSrc As Range
Dim rDst As Range
Set rSrc = wb2.Sheets("YourSheet").Range("YourRange")
Set rDst = ThisWorkbook.Sheets("SomeSheet").Cells("YourCell").Resize(rSrc.Rows.Count, rSrc.Columns.Count)
rDst = rSrc.Value

edited May 18 '15 at 13:08

shA.t

16,580
5
54
111

answered Mar 02 '11 at 04:41

chris neilsen

52,446
10
84
123

1

If you turn off Application.DisplayAlerts, do make sure you switch it on again (even in error cases). Otherwise the next time someone hits close and expects to see a "would you like to save" message might be disappointed. – Chris Rae Mar 04 '11 at 17:05
2

One issue with the "one line" method is that one cannot specify "xlpastevalues" - back to the clipboard it is! – dusio May 02 '13 at 08:59
@dusiod to copy values only you don't need to use copy/paste at all. See my edit – chris neilsen May 02 '13 at 11:04
@user3307245 you are (partialy) correct. Updated answer now works as intended (note: other users rejected your edit) – chris neilsen Feb 14 '14 at 09:59
1

The "direct copy" used the clipboard anyway. If you open a Notepad during macro execution and do some Paste, you will get data from your spreadsheet. – Fabien TheSolution Jun 21 '19 at 15:07

score 18 · Answer 2 · answered Jun 25 '14 at 08:32

18

If I may add one more solution: you can simply cancel the clipboard with this command:

Application.CutCopyMode = False

answered Jun 25 '14 at 08:32

Sergiu

349
4
8

Excellent ! Thanks – Pierre Bonaparte Jul 12 '18 at 21:17
In my tests, the location of the line above seems to be important. For example, if I perform other operations between the actual pasting and this line, the clipboard prompt keeps appearing, but if I put the line **immediately** after the line where I do the paste, then it works perfectly. I'm not sure why it happens the way it does or if this can be replicated, just thought it should be mentioned, just in case... – Yin Cognyto Dec 22 '19 at 01:09

score 4 · Answer 3 · edited Mar 20 '20 at 13:19

4

I have hit this problem in the past - from the look of it if you don't actually need the clipboard at the point that you exit, so you can use the same simple solution I had. Just clear the clipboard. :)

ActiveCell.Copy

edited Mar 20 '20 at 13:19

feetwet

3,248
7
46
84

answered Mar 04 '11 at 17:04

Chris Rae

5,627
2
36
51

score 0 · Answer 4 · edited May 18 '15 at 13:10

0

If you don't want to save any changes and don't want that Save prompt while saving an Excel file using Macro then this piece of code may helpful for you

Sub Auto_Close()

     ThisWorkbook.Saved = True

End Sub

Because the Saved property is set to True, Excel responds as though the workbook has already been saved and no changes have occurred since that last save, so no Save prompt.

edited May 18 '15 at 13:10

shA.t

16,580
5
54
111

answered Jul 24 '14 at 13:27

Anil Chahal

2,544
2
22
19

score 0 · Answer 5 · answered Jan 17 '17 at 11:25

0

There is a simple work around. The alert only comes up when you have a large amount of data in your clipboard. Just copy a random cell before you close the workbook and it won't show up anymore!

answered Jan 17 '17 at 11:25

kid

13
2

1

it may have been a large amount when Excel first came out, now it's often a negligible amount. – mcmillab Aug 29 '18 at 03:46

score 0 · Answer 6 · answered Oct 08 '18 at 23:07

0

Just clear the clipboard before closing.

Application.CutCopyMode=False
ActiveWindow.Close

answered Oct 08 '18 at 23:07

Raleigh Paenitz

1

score -1 · Answer 7 · edited May 13 '16 at 08:43

-1

proposed solution edit works if you replace the row

Set rDst = ThisWorkbook.Sheets("SomeSheet").Cells("YourCell").Resize(rSrc.Rows.Count, rSrc.Columns.Count)

with

Set rDst = ThisWorkbook.Sheets("SomeSheet").Range("YourRange").Resize(rSrc.Rows.Count, rSrc.Columns.Count)

edited May 13 '16 at 08:43

TobiasR.

826
9
19

answered May 13 '16 at 08:11

Alessio Mistretta

1

Disable clipboard prompt in Excel VBA on workbook close

7 Answers7

Linked

Related