Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

prevent cell formatting change from pasted data

I have numerous spreadsheets with formatting set up in specific ways for specific reasons. Some are functional (conditional formatting to highlight duplicates, etc) while others are simply aesthetic. I want the spreadsheet to look professional. The problem is that I have a lot of users that paste data into these spreadsheets. I can't get them to 'paste values', they just ctrl-v paste. They're pasting from html or other spreadsheets, so it's carrying over the format. Is there any way I can protect my worksheet's formatting so they can paste data in without overwriting the formatting? I've searched the forums here and tried a few suggestions, but nothing I've found works.

If I can't protect the spreadsheet from formatting changes, is it possible to set up a keyboard shortcut using only excel, no addons, for 'paste values'?


Post your answer or comment

comments powered by Disqus
Guys,
here is a peculiar problem.
i am having a cell(text type) which is to be checked for length 20 chars.i am checking using a macro like

	VB:
	
 
    If Len(Trim(CStr(.Cells(14, 3).Value))) > 10 Then 
        MsgBox " Error:Error: Length of comments field cannot have more than  10 characters.", vbDefaultButton1 +
vbExclamation, "TMS Error" 
        Application.EnableEvents = False 
        .Cells(14, 3).Select 
        Application.EnableEvents = True 
    End If 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
i can do this without a macro i.e using data->validation->text length is less than 20 etc.. but what happens is when the user copy some text(has more than 20 chars) from other text editor(like notepad) and copy into this cell, then it will allow him.which is wrong. so i have to use a macro.
this cell has got some border too.
problem 1#) when i copy data from notepad to this cell this border is lost.
how can i prevent the border being lost.
problem 2#) when i paste data from notepad to this cell its type is changed to General(from text) and the cell is getting locked(but my sheet is protected) . how can i avoid this?
thanks alot for ur patience
regards,
Mahesh

Hi I am copying data from one sheet and pasting it into another
workbook.

Range("A1").Select
Selection.CurrentRegion.Select
Selection.Delete
Rows("1:1000").Select
Selection.Delete
Workbooks.Open Filename:="p:pro65csr01.xls"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
ActiveWindow.Close
Range("A1").Select

An example of the dates Before and After follows:
Note I require DD/MM/YYYY format
Note some cells have " - -"
BEFORE
- -
- -
1/11/2004
18/10/2004
10/08/2004
15/12/2004
10/12/2004
13/12/2004
12/11/2004

AFTER
- -
- -
11/01/2004
18/10/2004 PROBLEM
8/10/2004 PROBLEM
15/12/2004
12/10/2004 PROBLEM
13/12/2004
11/12/2004 PROBLEM

The problem:
If DD < 12 the the format changes from DD/MM/YYYY to MM/DD/YYYY

PS if I copy and paste the data manually I don't get the problem.

Can a cell that refers to the contents of another cell, adopt the the same
format as the reference cell and can the format change as the reference cell
format changes? Specifically can a cell's text attributes such as bold,
italics or text or cell color be assigned by and change with the reference
cell?

Hi, Please see the attached "sample.xls".

In cell "D1", after I typed in some text and pressed enter, the cell format changed. It only happened for texts not for numbers. There's no macro or conditional format that I could find. How did this happen, please help!!!

Hi everybody,

I have written a macro that works correctly. but, something weired happens after running the macro. some cells format change automatically after macro run. for example, some cells font become bold some get blue font color.

how can I solve that problem ?

Thanks

Iman

I have written a simple macro to copy a worksheet containing charts, from one workbook to another. The problem is when I copy and paste it into the new workbook the charts still reference the original workbook. Is there anyway to prevent the charts from doing that? I want them to keep their basic cell reference. I suppose I could write code to change the cells the charts are referencing but there seems there should be an easier way. Thanks.

When I paste data from information available on the web and paste it into
Excel the page view changes by removing the visible cell borders. This makes
for a strange looking screen and makes cell selection tricky.

How can I restore the worksheet to the standard format?

I have a spreadsheet that users paste reference numbers into from another
system. The numbers are either 4, 5 or 11 digits long. Because a lot of the
11 digit numbers begin with a zero that cannot be dropped the cell format has
to be text.

Whenever the user pastes an 11 digit number beginning in a zero the cell
format in excel gets changed from 'Text' to 'General' and the zero is lost

Any ideas on how I can lock the cell format. Excel 2000 user

I have tried many ways of changing formatting to retain the dates in cells when I copy a a column of cells from one sheet to another

I only have problem with one column (column D) when i copy and paste to another worksheet. It does not retain the original dates. It changes the dates to a date that is unusual.

Any solutions appreciated

I'm trying to come up with some VBA code that prevents a font color change to a particular cell in my worksheet. Basically, I don't want to user to modify the formatting at all, but color is most important. Any ideas?

Thanks a ton!

I am having trouble putting formulas into a cell. I copy a formula into a
cell and then the cell I copied it froms formatting changes the moment I
press return to leave the cell. It shows the formula not the result. This
happens in both the cell I copied from and paste into. The only way I know
to set things correct os to undo.

What is happenning?

Thanks.

Sounds like the cells you're pasting into are formatted as Text. XL
doesn't parse Text entries.

Format the target cells as General.

In article <8278400F-1DE1-4CF7-99CA-6DBF4E4478C2@microsoft.com>,
Todd <Todd@discussions.microsoft.com> wrote:

> I am having trouble putting formulas into a cell. I copy a formula into a
> cell and then the cell I copied it froms formatting changes the moment I
> press return to leave the cell. It shows the formula not the result. This
> happens in both the cell I copied from and paste into. The only way I know
> to set things correct os to undo.
>
> What is happenning?

I have attached a small version of my workbook in hopes that it will better illustrate my problem. I have a large data table from which I run several pivot tables each month.

I have a macro that I run to update the pivot table, an example of such a pivot table is in the workbook.

The macro has worked in the past just fine, but for some reason, the formats of my cells have changed so that the default format is now "Date" for any cell that contains only numbers. My focus right now is the Qtr Month field. It is supposed to return 1,2 or 3. So when my macro runs, an input box requests the user to enter the relevant quarter month (1,2 or 3) and updates the pivot table accordingly. Now, the macro does not run, because the actual Qtr Month value is 1-Jan, 2-Jan, or 3-Jan.

I have changed the cell format in the original data table, but it doesn't seem to affect the pivot tables. Even after refreshing. What did I do to make this happen, and how can I fix it?

I have created a spreadsheet to use as a Master Form and many of the calls
are use as title boxes. Some of them will include Hyperlinks. At times I will
change or remove a Hyperlink, but when I do, that cells format changes. So,
my question is how do I prevent this from happening?

I am using a worksheet that someone else set up. When I enter information in
a cell the format changes. I clicked "Undo" and then checked cell format
for the number and alignment format and both are set to general and there is
an upper border to the cell. When I enter text the border disappears and the
text is centered. I cannot determined what is causing this to happen. Also
the person who set up the worksheet is no longer with the company. The
conditional formatting is not turned on.

I want to set up a macro that copies a range to a new range, copying formulae
and cell formats, but ignoring raw data existing in the range copied from. In
other words I'm expanding a blank copy for a new period. One way would be to
name a blank copy in a range elsewhere and copy it in at the cursor, but for
various reasons I'd prefer not to do it that way this time.
I have made a 2 stage copy procedure, using the "Paste Special" Alt ESF and
Alt EST routines, but this process also copies raw data, presumably seeing
the data as a label, even although I have formatted the relevant cells as
numbers.

Has anyone encountered and solved this problem?

K

Using an excel spreadsheet as data in a mail merge, date format changes from
dd/mm/yyyy to mm/dd/yyyy

Hello,

I have about 150 cells, each with different info. I need to add the name "james" to each cell without changing the existing data. Is there a way to do this without pasting it into each cell individually?

Thanks in advance for your help.

I am using a worksheet that someone else set up. When I enter information in
a cell the format changes. I clicked "Undo" and then checked cell format
for the number and alignment format and both are set to general and there is
an upper border to the cell. When I enter text the border disappears and the
text is centered. I cannot determined what is causing this to happen. Also
the person who set up the worksheet is no longer with the company. The
conditional formatting is not turned on.

I have created a shared form for my company. On the form are named merged ranges for data entry. There are times when the information would be the same on some of the entries. When I copy and paste data from a named merged range to another named merged range the cell formatting changes from merged to unmerged thus changing the way the data is displayed. How could I avoid this?

I want to set up a macro that copies a range to a new range, copying formulae
and cell formats, but ignoring raw data existing in the range copied from. In
other words I'm expanding a blank copy for a new period. One way would be to
name a blank copy in a range elsewhere and copy it in at the cursor, but for
various reasons I'd prefer not to do it that way this time.
I have made a 2 stage copy procedure, using the "Paste Special" Alt ESF and
Alt EST routines, but this process also copies raw data, presumably seeing
the data as a label, even although I have formatted the relevant cells as
numbers.

Has anyone encountered and solved this problem?

K

Hello,
I have this serious problem and I would like to ask you for your help: Excel 2007 randomly changes cell formatting when i am saving an excel file, so when I re-open it, all numbers are formatted as dates. This is being done totally randomly in about 5-10% of savings and in all different files i am using.

I noticed that when this happens, excel pre-formatted Normal Cell Style changes from general number to date. I found also a lot of different people complaining about this error on the internet, but didn't find any solution to fix this, except to downgrade from 2007 to office 2000. :-)

Could you please tell me if someone else experienced same problem and if anyone knows how to fix that? Thank you.

I am using Windows XP and Office 2007 Professional Plus with all latest updates.

Thank you for your help!

Hi,

This is, probably a refinement, but I would like to understand why.

1) I have a cell formatted to accounting "£", then I put this VLOOKUP formula:

"IF(ISERROR(VLOOKUP(C8,PR!$D$28:PR!$E$36,2,FALSE)),0,VLOOKUP(C8,PR!$D$28:PR!$E$36,2,FALSE)))" I'm getting with this formula a specific value, the cell maintains the format, regarding accounting "£", but it changed the font, to verdana.

2) Then I needed to add other value to the same cell and I said to that cell, if the value on C8 is "0", then return "0" if not add the other value, I got this:

"IF(C8=0,"0",PR!$E$25+IF(ISERROR(VLOOKUP(C8,PR!$D$28:PR!$E$36,2,FALSE)),0,VLOOKUP(C8,PR!$D$28:PR!$E$36,2,FALSE)))". on this case the format accounting disappear and the value shown is "0" aligned to the left.

I don't know how to solve this because I don't understand why.

Can somebody give me light on this?

thanks in advance
João

Is there a way to allow other users of my spreadsheet to enter information in a cell, but forcing them to enter it in the format I want? i.e. I want a monetray value entered without the £ sign, but a users tendency is to put the £sign at the front. Can the cells be locked so that it will not allow input unless the correct format is used?


No luck finding an answer? You could always try Google.