Search code examples
vbaexcelclipboard

Disable clipboard prompt in Excel VBA on workbook close

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?

Solution

  • 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