Maybe furnishing you the Range I used to copy (Source) into
A like-kind (Destination) pre-formatted range (15 cells - as below)

A B C
1 Me BLANK 1.00
2
3 You BLANK 2.00
4
5 Someone BLANK 3.00

In the immediate window when I do:
? myvalue.address (rtn)
I get $A$1:$C$5

I guess that myvalue is a Variant since undefined
I was just curious if I could see how it looked while
Being stored in the myvalue variable;

Thanks for your help.

Happy Holidays,

Jim


"Gord Dibben" <gorddibbATshawDOTca wrote in message
:

Jim

?myvalue(enter) in Immediate Window shows the value of the copied cell.

Not sure why you get the error.

I don't profess to be a VBA whiz but looks like the Undo unpastes so's the
formatting doesn't get overwritten then the Target = myvalue becomes the new
value.

Similar to Range("A10") = Range("A1").Value which doesn't remove formatting from
A10 but does change the value to that of A1


Gord


On Fri, 22 Dec 2006 22:19:55 +0000, "JMay" wrote:

Gord -- Thanks for the code; Set up an example an decided to further
My understanding by "Stepping-Thru" the code.
I notice immediately that the Formatting (whivh previously existed - Is
Destroyed) soo.....

After doing the Paste
I have my code break set at the line .EnableEvents = False
After stepping thru the next line myValue = Target.Value (in the
Immediate window I want to see the value of myvalue by entering:
? myvalue (return) I get a R/T error type 13 - Type: Mis-match

Why is that?

The .undo UNPASTES what was pasted In (Undoing what was last done)
I see that, but then as the Target is assigned the Original values
The previous formatting is maintained

Thanks,

Jim May


"Gord Dibben" <gorddibbATshawDOTca wrote in message
:

Roger

Found this event code posted by someone you could use.

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 sheet tab and "View Code".

Copy/paste the above into that module.


Gord Dibben MS Excel MVP

On Fri, 22 Dec 2006 10:50:01 -0800, RogueBiscuit
wrote:

Thanks for your response. I know how to do that. I'm looking for something
a little different.

I work in finance and use financial statements all day. I have a "shell"
template for income statements and the like. I want to lock the formatting
of the shell (meaning borders, bolding, font size and such) and be able to
put data in without doing PasteSpecial.

"CLR" wrote:

Copy PasteSpecial Values, or Copy PasteSpecial Formulas, either
should leave the formatting intact...........

hth
Vaya con Dios,
Chuck, CABGx3



"RogueBiscuit" wrote:

When I put together spreadsheet I often have formatting that I would like to
be static. Then I can copy and paste values and formulas from other cells in
the worksheet without altering the format in the destination cell. Can
anyone help me do this? Thanks

Andrew

Gord Dibben MS Excel MVP