Macro for paste special on cut/drag

New Contributor

Hi,

      Had to solve a problem of having paste special functionality not only on copy of a column/selection but on cut/ insert of it. Tried to record what's happening when we copy and paste special and implement the same on cut but paste special was not working and my validations are not working. 

2 Replies

@AkhilDeepak 

With respect, since there is no code, the helper has to guess how it is structured and without knowledge of the exce version, operating system and storage medium, it can be nerve-wracking :).

This is also usually the reason that nobody reports, since it takes too much time to even make the problem recognizable. 

In this link you will find some more information about it:

Welcome to your Excel discussion space!

 

Possible workaround...

It may well be that Excel clears its internal clipboard when the file is closed.

As a result, you must first paste the data before closing the source file.

This means that .PASTE (or .PASTESPECIAL) must be done before ActiveWorkbook.Close.

However, you would then have to reference .PASTE completely and also specify Workbook and Sheet.

 

Thank you for your understanding and patience

 

NikolinoDE

I know I don't know anything (Socrates)

 

@NikolinoDE 

 Understood will try to explain our problem in much detail below : 

Our app creates an excel which has multiple columns with validations on them eg. what kind of values are allowed and dropdowns for few of them. If user enters something that doesn't fit the validation, cell color changes to intimate that. Issue we have is if user moves the column in excel (either by cut paste or drag and drop), the validations get messed up. It picks the validation of column where it has been moved. When we Copy and use Paste special validations are retained, but we can't expect all the users to do it that way. 

 

And right now I've recorded macros when we try to do these operations and changed the code to match our requirements which was not working as I said earlier. I've tried to mimic the same formula for copy and paste special on cut/drag.

Sub Macro3()
'
' Macro3 Macro
'

'
    Range("BS2:BS7").Select
    Selection.Cut
    Range("BU2:BU7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BU7").Select
End Sub