Menu
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?
791
To avoid seeing this message, you can 1) Save the file first, 2) Change the DisplayAlerts property, 3) Use the SaveChanges argument of the Close method, or 4) set the Saved property to True. Note that this won't save the changes, it will close the workbook without saving.
DisplayAlerts = False: This is a property of the application object. See here we have called it using “.” operator just. This line disables all alerts of the closing file, overwriting, or opening an already open file.
Steps to Close a Workbook Specify the workbook that you want to close. Use the close method with that workbook. In the code method, specify if you want to save the file or not. In the end, mention the location path where you want to save the file before closing.
I can offer two options
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
You can prevent all alert pop-ups by setting
Application.DisplayAlerts = False
[Edit]
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
If I may add one more solution: you can simply cancel the clipboard with this command:
Application.CutCopyMode = False
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
