4

I am copying rows (sometimes as little as 8 X 17) of data from multiple Excel files into one worksheet in another Excel file using these commands:

wksInput.Range(strInputRange).Copy
wksOutput.Range(strOutPutRange).PasteSpecial xlPasteValues

I receive a warning message stating "There is a large amount of information on the Clipboard. Do you want to be able to paste this information into another program later?".

Application.DisplayAlerts = False, does not work.

Is there a way to turn off this warning message?

Community
  • 1
  • 1
David F
  • 61
  • 1
  • 2
  • 7
  • You may want to find a code that clears the clipboard, seems like there are lots of those questions in this forum. – Davesexcel Oct 09 '18 at 18:01
  • 3
    Possible duplicate of [What exactly is the function of Application.CutCopyMode property in Excel](https://stackoverflow.com/questions/17607412/what-exactly-is-the-function-of-application-cutcopymode-property-in-excel) – BigBen Oct 09 '18 at 18:07

3 Answers3

4

This is not an excel specific issue as I've seen the Clipboard warning whenever you copy large data from other applications as well. However, when using excel, you might have a few ways to work around it using VBA.

There are several methods for preventing the display of this warning message. The quickest manual method is to press the ESC key just before you close the workbook.

In an automated scenario that uses a Visual Basic for Applications macro to cut or copy cells, you may not consider it an acceptable option to press the ESC key to prevent the warning. In that case, use any of the following programmatic methods to prevent the warning.

Method 1: Copy A Single Cell

If you are using Visual Basic for Applications macro to cut or copy cells, insert the following line immediately before the line that closes the workbook:

ActiveSheet.Range("A1").Copy

The warning message is not displayed if the Clipboard contains 100 or fewer cells.

Method 2: Exit from CutCopyMode

If you are using Visual Basic for Applications macro to cut or copy cells, insert the following line immediately before the line that closes the workbook

workbook.Application.CutCopyMode = False

where "workbook" is your workbook object.

NOTE: By setting CutCopyMode to True or False, Excel may cancel Cut or Copy mode.

Method 3: Save the Workbook

If you are using Visual Basic for Applications macro to cut or copy cells, insert the following line immediately before the line that closes the workbook

workbook.Save

where "workbook" is your workbook object. When you save a workbook, Excel is no longer in Cut or Copy mode.

Original article can be found on this Microsoft support page

Community
  • 1
  • 1
ParvBanks
  • 1,316
  • 1
  • 9
  • 15
1

Did you already try something like this:

wksOutput.Range(strOutPutRange).Value = wksInput.Range(strInputRange).Value

(Maybe you might need to loop over all cells in the ranges)

Like this, you don't need the clipboard, so you won't be bothered by the message.

Dominique
  • 16,450
  • 15
  • 56
  • 112
0

This appears as soon as you close the workbook. So use Application.CutCopyMode = False after you close the workbook.

The code should look something like this:

wbk.close False 'code for closing the workbook in vba

Application.CutCopyMode = False 'Code for removing the cutcopymode and the warning
ouflak
  • 2,458
  • 10
  • 44
  • 49
madhwaraj gururaj
  • 21
  • 3