Excel macro-Clipboard answer

How do I write my Excel 2000 macro to automatically answer the following question "YES"?  "There is a large amount of information on the Clipboard.  Do you want to be able to paste this information into another program?" Yes/No/Cancel are choices.
This pops up whenever I try to close one spreadsheet (csv file) after I have copied its entire contents to the clipboard for pasting into another spreadsheet.
mcqjAsked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
If you need all cells, then you really want to do a worksheet copy operation, don't you?  

The following two examples copy without using the clipboard, since I specify the destination with the copy operation.

Example of copying an entire worksheet:
Worksheets("Sheet1").Copy after := Worksheets("Sheet3")

Example of copying the used range to another worksheet:
Worksheets("Sheet1").UsedRange.Copy destination:=Worksheets("Sheet3").Range("A1")
0
 
aikimarkCommented:
I'm not sure that can be done exactly as you wish.

You can use the DataObject in place of the Clipboard object if your Excel VBA code is doing the movement of data.  If you're doing the copy/paste by hand instead of programmatically, you're out of luck.

You might include code in the worksheet close event that will copy the contents of the clipboard to the dataobject and clear the clipboard.

Be warned: unlike the clipboard object, the Dataobject contents aren't persistent beyond the life of the Excel program.
0
 
mcqjAuthor Commented:
Could you please illustrate the movement of all cells of one spreadsheet into another spreadsheet using the DataObject in place of the Clipboard?

The best I've been able to do so far is to use:
Application.DisplayAlerts = False
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.