Clipboard Question

[Himansu]

Hello everyone,

I have a section of a macro that imports a file
and copies the contents of the entire worksheet
into another workbook. I keep getting this
prompt message:

'There is a large amount of information on the
Clipboard. Do you want to be able to paste
this information into another program later?'

* I cleared all the contents on the clipboard and
in cache memory, but every time I execute the
macro I keep getting this prompt. Is there any
way to just default to 'yes' and remove this?
Is this happening because I'm copying the entire
worksheet?

Any input will be greatly appreciated.

Thanks,
Himansu

 

[dominicb]

Good afternoon Himansu

Try inserting this line after you've done your copying:

Application.CutCopyMode = False

HTH

DominicB

 

[Himansu]

Hello DominicB,

Tried your suggestion. I got an error. Here's
a part of the code:

--
Columns("A:A").Select
Cells.Select
Selection.Copy
Range("A1").Select
Application.CutCopyMode = False ' added 12/05/05 12:05 PM EST
ActiveWindow.Close
ActiveSheet.Paste
--

* Let me know what you think.

Thanks,
Himansu

 

[dominicb]

Hi Himansu

You put the line in the wrong place. You copied data onto the
clipboard, and then effectively cancelled it with the CutCopyMode =
False statement.

Using your code, I switched the lines around a bit to give me this:

Columns("A:A").Select
Cells.Select
Selection.Copy
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False ' added 12/05/05 12:05 PM EST
ActiveWindow.Close

which works without a hitch.

HTH

DominicB

 

[Himansu]

Hi DominicB,

Once the contents of the cells are (1) copied
that worksheet is (2) closed, then the data
that is copied is (3) pasted into the active worksheet.

Let me know if you need me to elaborate more
on this.

Thanks,
Himansu

 

[Guest]

Here is some code for you. You need to know the name of the sheet and book
that you intend to copy the data into for this to work. That is a whole lot
safer than pasting to the active workbook once the current window is closed...

Sub Test()
Dim wbkSource As Workbook
Dim wbkDestination As Workbook
Dim wksSource As Worksheet
Dim wksDestination As Worksheet
Dim rngSource As Range
Dim rngDestination As Range

'Set your source
Set wbkSource = ThisWorkbook
Set wksSource = wbkSource.Sheets("Sheet1")
Set rngSource = wksSource.Cells

'Set your destination
On Error GoTo OpenBook
Set wbkDestination = Workbooks("ThatBook.xls")
On Error GoTo 0
Set wksDestination = wbkDestination.Sheets("Sheet1")
Set rngDestination = wksDestination.Range("A1")

'You now have all of your souce and destination objects

rngSource.Copy rngDestination

Exit Sub

OpenBook:
Set wbkDestination = Workbooks.Open("C:\Thatbook.xls")
Resume Next
Exit Sub

End Sub

 

[Himansu]

Jim,

Thanks for the info. I'll embed this into my
code. I have a lot more re-work to do now.
* Your idea makes sense.

Thanks,
Himansu