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

User · Answer

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

User · Answer

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     lt YourRange gt   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     lt YourRange gt   Copy ThisWorkbook Sheets  SomeSheet   Cells  lt 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Just clear the clipboard before closing    Application CutCopyMode False ActiveWindow Close

[vba] Disable clipboard prompt in Excel VBA on workbook close

Examples related to vba

Examples related to excel

Examples related to clipboard