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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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...
 

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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
 

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613

ADVERTISEMENT

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
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

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
 

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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
 

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,096
Messages
5,768,068
Members
425,451
Latest member
JohnBrooksBiddle

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
Top