Urgent - Ctrl-V between sheets, not allow Drag and drop

G

Guest

Hi,
I really need to get this working, so please help me if you know a solution.
I want to be able to do Crl-C and Ctrl-V with data between sheets, but I
have a Application.CellDragAndDrop = False within my Worksheet_Activate Sub.

It seems like the celldraganddrop clear the clipboard. Within the sheet, It
is all ok. to make Ctrl-C / -V. But I have to paste between sheets and I
have to not allow celldraganddrop.

Is there a way to keep clipboard content in antother "mode" and pick it up
when needed? Can I use another event to stop Draganddrop? What?? This is how
the sub look now and if I take away CellDragAndDrop line, i can paste from
another sheet as I need. But...

Private Sub Worksheet_Activate()
On Error Resume Next
Application.CellDragAndDrop = False
Application.ScreenUpdating = False
Application.EnableEvents = False
.... ...

/Regards
 
D

Dave Peterson

Maybe you could give them a macro to do the copy|paste.

Option Explicit
Sub copyEm()

Dim rngToCopy As Range
Dim rngToPaste As Range

Set rngToCopy = Nothing
On Error Resume Next
Set rngToCopy = Application.InputBox(Prompt:="Range to copy", _
Default:=Selection.Address(0, 0), _
Type:=8).Areas(1)
On Error GoTo 0

If rngToCopy Is Nothing Then
Exit Sub
End If

Set rngToPaste = Nothing
On Error Resume Next
Set rngToPaste = Application.InputBox(Prompt:="Top left cell to Paste", _
Type:=8).Cells(1)
On Error GoTo 0

If rngToPaste Is Nothing Then
Exit Sub
End If

rngToCopy.Copy _
Destination:=rngToPaste

End Sub
 
G

Guest

Thank you Dave for answer my cry for help. I'll take your suggestion in
mind.

Does you or anybody else know what commands wipe out the clipboard? I havn't
found any information about that anywhere.

I think pasting between sheets will work out if I don't touch draganddrop.
Maybe I just have to accept the draganddrop and do a lot of coding to
reconstruct formulas and formats from a template every time a change occurs.
Drag anddrop will ruin the link references and formats, but I might be able
to rebuild it every time....

/Regards
 
D

David McRitchie

From another David, (posted to programming)

You could record a macro and get the answer.
Application.CutCopyMode = False

Chip Pearson has a page that has Clipboard material
http://www.cpearson.com/excel/clipboar.htm

but it does cover either of these in his article:
Application.CommandBars("Clipboard").Visible = True
Application.CutCopyMode = False

I guess it's not strictly within to first sentence description:
This page describes various methods in Visual Basic For
Applications (VBA) for copying data to and retrieving data
from the Windows clipboard.

It would be a lot more friendly if you included your first
and last name in the newsgroups.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Thank you Dave for answer my cry for help. I'll take your suggestion in
mind.

Does you or anybody else know what commands wipe out the clipboard? I havn't
found any information about that anywhere.
Click to expand...
=
 
Top