Problem with Paste Special

[pbt]

I have the following in my Workbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'If you have any worksheet to exclude
If Sh.Name = "Sheet2" Then Exit Sub
With Target
     If Len(.Value) <> Len(Trim(.Value)) Then
    
         MsgBox "You just entered a leading space character in" & vbCrLf & _
         "                     cell " & .Address(0, 0) & "." & vbCrLf & vbCrLf & _
"If you intend to delete the value in that or any cell, " & vbCrLf & _
"please press the Delete button on your keyboard.", 16, "         No leading spaces allowed !!"

          Application.EnableEvents = False
             .Value = Trim(.Value)
          Application.EnableEvents = True
     End If
End With
End Sub

When I tried to Copy>Paste Special > Formats or Values of more than One row this line highlights

 If Len(.Value) <> Len(Trim(.Value)) Then

That is, if I select C3:Q3, Copy, then select C10:C15 and try to do a Paste Special that is when it gives me an error 13: Type mismatch.

If I just select C10 and do the Paste Special, it's just fine.

Any help appreciated. I've been fooling with this for at least 3 hours with no luck.

Harry

 

[jindon]

Try paste to the range with the same horizontal size (15 columns) with various row length.

 

[pbt]

Okay, I think I figured it out.

I openened a new book and formated some cells with color and borders and put text in a few of the cells.

I played around with different senarios of Paste Special to see how it works. I think I know how it works now.

I tried to explain on this thread, for others, but can't get it into the right words. So I deleted that part.

It all boils down to selecting the right range to copy and the right range to paste.

I guess after 6 hours on this one thing, the brain just doesn't function.

Thanks for your time, jindon

Harry