Force Paste Special Values

[hatman]

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:

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?

 

[hatman]

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...

 

[hatman]

Nevermind....Tried the Workbook sheet and that did the trick. This rocks!

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.

ENJOY!

 

[HRIS]

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

 

[Walky]

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.

 

[HRIS]

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:

.Cells.SpecialCells(xlVisible).Copy

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

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.

 

[Walky]

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.

 

[HRIS]

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

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

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.

 

[Walky]

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.

 

[HRIS]

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!

 

[Walky]

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.