How do I lock in formatting on a worksheet
 

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

How do I lock in formatting on a worksheet
 

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

How do I lock in formatting on a worksheet
 

Press F4 on a cell until you see $A$1. I use A1 as an example

"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

How do I lock in formatting on a worksheet
 

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

How do I lock in formatting on a worksheet
 

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.

"Teethless mama" wrote:

Press F4 on a cell until you see $A$1. I use A1 as an example

"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

How do I lock in formatting on a worksheet
 

So if i get you correctly you want to paste special without actually using
paste special??? For this particular spread sheet the destination formatting
always superceeds the incoming formatting. If that is the case then you are
out of luck.

Cells are self contained items that hold formulas, values, formats, etc...
When you copy them you get the whole thing. When you paste them (short of
using paste special) you paste the whole thing... That is not a feature that
can be overridden for a specific workbook or for a specific cell... What you
can do is to put the paste special buttons right on your tool bar and use
those buttons to speed up the paste special process.

The other option is to change your template to have two seperate sheets. One
is a data entry sheet where you put your values or formulas organized in a
way that is maximised for ease of entry. The format of the data on this sheet
is not important. The values from that sheet are processed onto an output
sheet (in your case the required finacial statement) which is formatted just
the way you want it. There is never any data entry on the output sheet. It is
entirely formula driven. A good analogy would be Tax prep software where they
ask you to fill in a bunch of info on a data enty sheet and then they use
that info to populate a pre-formatted tax form.
--
HTH...

Jim Thomlinson


"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

How do I lock in formatting on a worksheet
 

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

How do I lock in formatting on a worksheet
 

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

How do I lock in formatting on a worksheet
 

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

How do I lock in formatting on a worksheet
 

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