Problem with Paste Special

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
I have the following in my Workbook
Code:
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
Code:
 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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi
change to
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim r As Range
'If you have any worksheet to exclude
If Sh.Name = "Sheet2" Then Exit Sub
For Each r In Target
     If Len(r.Value) <> Len(Trim(r.Value)) Then
             MsgBox "You entry in" & vbCrLf & _
         "                     cell " & r.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
             r.Value = Trim(r.Value)
          Application.EnableEvents = True
     End If
Next
End Sub
edit the comment in the msgbox as you wish...
 
Upvote 0
At first I was having all sorts of problems, but then I refreshed this page and seen your Edit.

Using the same example as above:

If I choose just C10, works fine.

If I choose more that one new row to paste >formats or values to (C10:whatever), it will only paste to the Column C, with no errors.

Harry
 
Upvote 0
First of all the sheet is protected for the user to only be able to go into a certain range of cells. I have unprotected the sheet to add more "User" rows with the same formating.

I am trying to Copy C3:Q3 which is formated in a color and the cells are unlocked.

I then select C10:C15, Paste Special Format, to apply the same formating (color and unlocked)

All other cells (except the ones that are unlocked) in this sheet is locked and off limits.

This method is very common, as you know, and workd fine until I put the Workbook code in it.

Edit: If I remove the code, the above steps work fine.

Harry
 
Upvote 0
Can you do the same thing on the other sheet(new sheet) to test if what you are tryig to do is possible?

It soulds like you are trying to copy single horizontal range and paste to
vertical range with the same size in horizontal range as well.

I'm really not sure if this is possible or not.
 
Upvote 0
And if it is possible then try
Code:
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 Application.CutCopyMode Then Exit Sub '<- this line added
     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
 
Upvote 0
jindon,

It soulds like you are trying to copy single horizontal range and paste to
vertical range with the same size in horizontal range as well.

I'm really not sure if this is possible or not.

It is possible and is an Excell native function.

I opened another Workbook that is very similar to this one in question, only it did not have the Code in it.

I did the Copy Paste Format as describe above, worked fine.

I then copied your code above and placed in Workbook module and did the same thing, worked fine.

I think my problem in the workbook that I was testing on, I didn't do a copy of the code, just read through your post and modified my existing code. I must have missed something. SORRY

It works just fine now.

Harry
 
Upvote 0
DARN

I take that back. It does not work.

Same problem, if more then one row selected for the Paste, only the first column receives the format.

Harry
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top