Paste special function
 

I have 2 worksheets: one large one with data for several employees, and a
smaller one that will be sent to individual employees. I have the smaller
worksheet formatted in an easy to read format, and will be copying data from
the larger worksheet to the smaller one.

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?

Paste special function
 

You should create a paste special values macro like this and assign a
keystroke to it (via Tools, Macro, Macros, pick macro, Properties).

Sub DoPasteValues()
ActiveCell.PasteSpecial xlValues
Application.CutCopyMode = False
End Sub


--
Jim
"richzip" wrote in message
...
|I have 2 worksheets: one large one with data for several employees, and a
| smaller one that will be sent to individual employees. I have the smaller
| worksheet formatted in an easy to read format, and will be copying data
from
| the larger worksheet to the smaller one.
|
| 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?

Paste special function
 

As an alternative to Jim's suggestion you could place event code in the target
worksheet that preserves the formatting when anything is copied into it.

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


"richzip" wrote in message
...
|I have 2 worksheets: one large one with data for several employees, and a
| smaller one that will be sent to individual employees. I have the smaller
| worksheet formatted in an easy to read format, and will be copying data
from
| the larger worksheet to the smaller one.
|
| 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?