How to avoid "large data on clipboard" message?

[keith]

I have written some VBA code in excel 2000 which imports one of 30 text
files, copies an area 1 column by 78 rows onto my master data consolidation
sheet, closes the imported sheet then repeats with the next one.

All works fine, except that each time excel puts up a dialog box asking me
if I want to keep the large amount of data on the clipboard -(so I have to
answer "no" 30 times...)
Is there a way to suppress this message?

Is there something like:
Activesheet.Paste SuppressAnnoyingMessageAboutClipboard:=True ?

Thanks


code within the loop is :
Windows(sourcefile).Activate
Range("B2:B79").Select
Selection.Copy
Windows("test.xls").Activate
copydest = "data" + mydate
'copydest is one of several named ranges, eg data01, data02, data03...
Application.Goto Reference:=copydest
ActiveSheet.Paste
'message appears after here
Range("A1").Select

Windows(sourcefile).Activate
ActiveWorkbook.Close SaveChanges:=False

 

[JohnI in Brisbane]

keith,

This message gets displayed if there is still data in the clipboard from a
previous cut/copy.
It's a good habit to enter the following command after pasting (or whatever)
the data:-

Application.CutCopyMode = False

- to clear the clipboard. I've had some memory management problems when I
failed to do this.

If you still need the paste info. after closing, use:-

Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True

to prevent the message.

Personally I take the message as a warning that I've missed cleaning up
after a cut/copy.

regards,

JohnI

 

[keith]

keith,

This message gets displayed if there is still data in the clipboard from a
previous cut/copy.
It's a good habit to enter the following command after pasting (or whatever)
the data:-

Application.CutCopyMode = False

Personally I take the message as a warning that I've missed cleaning up
after a cut/copy.

Thanks everyone, that 's fixed it beautifully - saved me a whole lot of
frustration, really pleased, thanks.