Solved: Answer No to Alert When DisplayAlerts = False

[Belch]

Hi,

I have a routine that opens a workbook with a large amount of data on a worksheet, copies the data, pastes it into a worksheet in another workbook and then closes the original workbook. See the code below:


Workbooks.Open ("path for original workbook")
Workbooks("original workbook").Activate
ActiveWorkbook.Worksheets(1).Cells.Copy
Workbooks("destination workbook").Activate
ActiveWorkbook.Worksheets("destination sheet").Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks("original workbook").Close
Application.DisplayAlerts = True


Normally, when the original workbook is closed the alert regarding saving the Clipboard data appears - I have used "DisplayAlerts = False" to suppress this but of course DisplayAlerts answers each alert with the default option which in this case is "Yes", therefore saving the data to the clipboard. But there is a lot of data so this takes up a lot of memory.

So my question is: is there a way to suppress alerts but answer "No" to this specific alert?

Thanks,

[smc2911]

This should do the trick:
Workbooks.Open ("path for original workbook")
Workbooks("original workbook").Activate
ActiveWorkbook.Worksheets(1).Cells.Copy
Workbooks("destination workbook").Activate
ActiveWorkbook.Worksheets("destination sheet").Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.DisplayAlerts = False
Workbooks("original workbook").Close
Application.DisplayAlerts = True
This clears the clipboard.
Sean.

[Belch]

Works a treat, I don't even need the DisplayAlerts lines now.

Cheers Sean.

[smc2911]

No worries.
Sean.