Is there any way that 'filling' can be restricted to values only and not formatting, perhaps through VBA?

[Rnkhch]

Hello,

I know that it is possible to right-click and drag and then select 'fill without formatting', but is there any formatting changes can be disabled for dragging/filling, perhaps through a VBA code?

Thanks for any input!

 

[My Aswer Is This]

This script is supposed to do what??

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Not Application.CutCopyMode = False Then
Target.PasteSpecial xlPasteValues
Application.CutCopyMode = True
End If
End Sub

 

[MARK858]

@Rnkhch Is the first code your complete code? then if it is it needs to be in a Sub like you have posted with the 2nd code or a regular sub, if it is not your complete code then post the complete code please

 

[Rnkhch]

@ My Answer Is This: That script allows the users to do just a single click at the destination and paste the values of anything that was copied. So it prevents formatting transfer during copy/paste. You can see the details here (comment #27154 at the comments section at the bottom):

How to restrict to paste values only (prevent formatting) in Excel?

www.extendoffice.com

@ MARK858: The first code is just that 1 line that was suggested by JohnnyL at post #6

 

[My Aswer Is This]

Since now we have others here helping you, I will move on to help someone else who may need help.

 

[Rnkhch]

Incidentally, I tried the same 1 line of code in a new blank workbook and I highlighted cell E3 and dragged it down, and the highlight still carried over, so as far as I can see that one line of code on its own doesn't do anything? Am I missing something?

 

[johnnyL]

Read post #12 please.

 

[Rnkhch]

Oh I see, so it needs to be in a Sub? Could you please help me with that? I'm a newb at VBA and not sure how to get the syntax perfectly right for the Sub Thank you!

 

[MARK858]

How are you intending to run the code via a button / Alt+F8 or triggered by some action on the worksheet? if the latter what action?

 

[Rnkhch]

I would say triggred by acion, which would mean when the user is dragging any cell in the range of E3:E42

 

[MARK858]

dragging any cell in the range of E3:E42

If they were dragging the cell then they wouldn't need that macro

The code below runs if they double click any single cell in the range E3:E42

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("E3:E42")) Is Nothing And Target.CountLarge = 1 Then
        Application.EnableEvents = False
        Range("E3").AutoFill Destination:=Range("E3:E42"), Type:=xlFillValues
        Application.EnableEvents = True
    End If
End Sub

The code goes in the Worksheet module