Force Paste Special Values

hatman

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
HAH... I think i have asked more questions today than in all the time since I became a member... I guess that means I'm pushing the envelope.

Anywho. I have a workbook where I want to retain FORMATS in all of my cells in all of my sheets, but allow the user to enter data. So I got this code to undo PASTE and instead do a PASTE SPECIAL VALUES:

Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim UndoString As String

    On Error GoTo err_handler

    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
    If Left(UndoString, 5) = "Paste" Then 'Only allow Paste Special|Values
            Application.ScreenUpdating = False
            Application.Undo
            Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            Application.ScreenUpdating = True
    End If

    Exit Sub

err_handler:

End Sub

Only I had a co-worker submarine this code by simply entering some data, then dragging to fill across. All my formats went south. Any way to fix that?
 
Sort by date Sort by votes
The scope, as I have wpresented it above, is for an entire workbook. It should be placed in the ThisWorkbook Class Module complete as-is in its entirety..

If you wish the scope to encompass only a single sheet within the book, then you need to use the Sheet Class Module: select Worksheet from the Object Drop Down, select Change from the Procedure Drop Down, then paste the above code (omit the First and Last lines) in between the two lines of code that appear...
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Nevermind....Tried the Workbook sheet and that did the trick. This rocks!
Click to expand...

Glad you got it working. And I am very glad you found code that does the trick for you. I don't know which feels better: finding code that works without having to change it, or buiolding code that other people can use without changing. :lol:

ENJOY!
 
Upvote 0
This code worked out wonderfully for one of my projects!! Just wanted to say thanks for posting your final results!!!
 
Upvote 0
Hello,

I tried using Hatman's code, but unfortunately, if I copy filtered/grouped data, it pastes the UNfiltered/UNgrouped data of the range I selected.

Is it possible to modify the code in order to avoid the "expansion" of the selected data when I paste it?

Thx,

W.
 
Upvote 0
I'm not sure if there is a better way but how I've usually done it is when I'm copying - I only copy visible cells. I use something like the following line for copying filtered data:

Code: 
.Cells.SpecialCells(xlVisible).Copy

Does this make sense and is what you're needing?

Walky said:
Hello,

I tried using Hatman's code, but unfortunately, if I copy filtered/grouped data, it pastes the UNfiltered/UNgrouped data of the range I selected.

Is it possible to modify the code in order to avoid the "expansion" of the selected data when I paste it?

Thx,

W.
Click to expand...
 
Upvote 0
Hi HRIS,

Thanks for the reply. To copy my data, I simply... copy.

It's the RE-pasting done by HATMAN's code which does not keep the "visible" data as you correctly put it.

So in fact, the solution might be to insert/add the code you mention to HATMAN's code.

Can you please do that and I'll try it tomorrow when I get to work.

Rgds,

W.
 
Upvote 0
I believe it should work just by adding the "SpecialCells(xlVisible)" before the copy statement in the code. See below.


Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 
    Dim UndoString As String
    Dim srce As Range
 
    On Error GoTo err_handler
 
    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
 
    If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
 
        Exit Sub
 
    End If
 
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Undo
 
 
    If UndoString = "Auto Fill" Then
 
        Set srce = Selection
 
        srce.SpecialCells(xlVisible).Copy
 
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
 
        Application.SendKeys "{ESC}"
 
        Union(Target, srce).Select
 
    Else
 
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
 
    End If
 
    Application.ScreenUpdating = True
    Application.EnableEvents = True
 
    Exit Sub
 
err_handler:
 
    Application.ScreenUpdating = True
    Application.EnableEvents = True
 
End Sub







Walky said:
Hi HRIS,

Thanks for the reply. To copy my data, I simply... copy.

It's the RE-pasting done by HATMAN's code which does not keep the "visible" data as you correctly put it.

So in fact, the solution might be to insert/add the code you mention to HATMAN's code.

Can you please do that and I'll try it tomorrow when I get to work.

Rgds,

W.
Click to expand...
 
Upvote 0
Hello HRIS,

Unfortunately, your modification did not change the result: the data which is not visible in the source selection is still pasted.

I don't know if the fact that the pasting takes place on a different sheet from which the source data is, makes a difference...

I just figured-out that the solution to what I want, i.e. allow the pasting of only visible values on a specific worksheet, could simply lie in exposing what I'm after!

I'm trying to use hatman's code, but i may not be suited...

I tried unlocking the range into which data is to be pasted as values, then protecting the worksheet (I read that only values and number formats were retained) but it didn't work: the source formats were still pasted, but only visible data was pasted (as I require).

Any suggestions welcome.

Thanks,

W.
 
Last edited:
Upvote 0
I apologize but it may be a bit before I could look into this further. At this point I'd recommend starting a new thread describing exactly what you are wanting to do and the code as you have it now, and also include a link back to this thread if needed. That way you'd probably have some other (more knowledgable) folks taking a look at it and possibly helping you with your solution. I hope to look at it again when I get a chance but it may be a while before I can!
 
Upvote 0
Hello HRIS,

Thanks for the follow-up and the advice. I'll do so and try to clearly explain whan I need when I have a bit of time.

Rgds,

W.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Undo Stack gets cleared running VBA - Possible Solution to fix this?
Replies
5
Views
129
kellman
K
N
  • Question Question
How do I remove undo from code?
Replies
1
Views
555
noveske
N
B
  • Question Question
When removing links with copy and paste special some values appear in scientific notation or expanded decimal places.
Replies
7
Views
319
BYahr
B
G
  • Question Question
Disable Cut Copy Paste
Replies
1
Views
858
jkpieterse
jkpieterse
C
  • Question Question
Paste Special Values - VBA code
Replies
6
Views
360
jolivanes
jolivanes

Forum statistics

Threads
1,224,597
Messages
6,179,812
Members
452,945
Latest member
Bib195

Share this page

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