When copying the data to the smaller worksheet, the formatting I customized
gets written over by the formatting from the larger worksheet. I know I can
use "paste special" to only copy the values and not the format. However, is
there a way to make this the "default" option for pasting so that I can use
"alt-V" instead of right clicking?
Sub DoPasteValues()
ActiveCell.PasteSpecial xlValues
Application.CutCopyMode = False
End Sub
--
Jim
"richzip" <ric...@discussions.microsoft.com> wrote in message
news:E8EFE378-17A2-4132...@microsoft.com...
Private Sub Worksheet_Change(ByVal Target As Range)
'retain formatting when a cell is copied over
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
End With
End Sub
This is sheet event code. Right-click on the target sheet tab and "View Code".
Copy/paste into that sheet module. Alt + q to return to the Excel sheet window.
You can now copy from source sheet and paste to target sheet with no disruption
of target sheet formatting.
Gord Dibben MS Excel MVP