VBA to allow numbers only of copy/pasted data while keeping destination cell formatting

[JustMeAZ]

I’m limited in my Excel abilities and somehow got suckered into developing this spreadsheet for many different users to copy and paste number values only from their own source data.

There’s too many of them and we can’t make sure they will “paste values” only. So that’s not an option. I found VBA code that works to prevent the reformatting the destination cell with their source:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

On Error Resume Next

Target.PasteSpecial xlPasteValues

Application.CutCopyMode = True

End Sub

How, what, where can I add into this to make it so that the destination cells will not accept letters or characters or anything that isn’t a number (decimals are fine) when it is pasted instead of entered? In case it’s relevant, the range of these cells is broken up all across this spreadsheet. For example, it would apply to B9, B10, B11, B12 and then E9, E10, E11, E12. Then B15, B16 and E15, E16. And so on. So it’s consistently in the same columns, but not every single cell In the column.

I had done data validation on these cells but found they’re wiped out when copy and paste is used.

Thanks so much for any guidance!

 

[hajiali]

Add this code to The sheet. Right Click on the sheet name at the bottom of excel, and Click View Code. Paste the below code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Set rg = Range("B9:B12,E9:E12,E15:E16") ' specify all your range Here
    If Not Intersect(Target, rg) Is Nothing Then
        If Not IsNumeric(Intersect(Target, rg)) Then
            Application.EnableEvents = False
            Application.Undo
                MsgBox "ONLY NUMBERS ARE ALLOWED IN THIS CELL", vbOKOnly, "INVAILD VALUE" ' MsgBox row is Optional
            Application.EnableEvents = True
                Exit Sub
        End If
    End If
End Sub