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

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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
 
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
 
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.


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...
=
 
Back
Top