22

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?

Improve this question
1
  • 4
    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). Commented Mar 3, 2011 at 12:08

7 Answers 7

Reset to default
48

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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.
One issue with the "one line" method is that one cannot specify "xlpastevalues" - back to the clipboard it is!
@dusiod to copy values only you don't need to use copy/paste at all. See my edit
@user3307245 you are (partialy) correct. Updated answer now works as intended (note: other users rejected your edit)
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.
19

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

Application.CutCopyMode = False
Improve this answer

1 Comment

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...
5

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
Improve this answer

Comments

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.

Improve this answer

Comments

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!

Improve this answer

1 Comment

it may have been a large amount when Excel first came out, now it's often a negligible amount.
0

Just clear the clipboard before closing. 

Application.CutCopyMode=False
ActiveWindow.Close
Improve this answer

Comments

-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)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.