Disable clipboard prompt in Excel VBA on workbook close

19
votes

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?

7
vbaexcelclipboard
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

7 Answers

42
votes

I can offer two options

  1. 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
  1. Suppress prompt

You can prevent all alert pop-ups by setting

Application.DisplayAlerts = False

[Edit]

  1. 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
18
votes

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

Application.CutCopyMode = False
3
votes

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
0
votes

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.

0
votes

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!

0
votes

Just clear the clipboard before closing. 

Application.CutCopyMode=False
ActiveWindow.Close
-1
votes

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)