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

Use:

Application.DisplayAlerts = False

This will shut off all messages. Turn it back on after your cut/paste
if you want to restore messages. I generally leave it off until the end
of the module.

Mike



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Add one row below ActiveSheet.Paste

Application.CutCopyMode = False


-----Original Message-----
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




.

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" wrote in message
...
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
Application.CutCopyMode = False ' <- Add this command.
Range("A1").Select

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

"JohnI in Brisbane" wrote in message
...
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.