Excel formatting problem

ladyfear

New Member
Joined
Oct 6, 2008
Messages
5
Hi all,

I am using Excel 2003 and am having a problem formatting. Specifically, when I copy from one spreadsheet and paste special/values to another spreadsheet, sometimes the formats from the original spreadsheet are also pasted. Why is this and how can I fix it? I have looked at the protections on the original, have tried the "Calc Now" button, etc. Any insight provided would be greatly appreciated. Thanks!

Ladyfear
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Hi, welcome to the board.

Which formating feature comes across ? Number format ? Font ? Colours ? Can you give us specific examples of the data that is afected ?
 

ladyfear

New Member
Joined
Oct 6, 2008
Messages
5
For example, let's say I'm copying a column of numbers from WB1 to WB2. The original values are derived from formulas, formatted in "Comma". When I paste special/values to WB2, I expect the values to be formatted in the formats set in the new sheet, say, "Accounting." But for whatever reason, the "Comma" format is prevalent. When I manually try to format the pasted data in WB2, the new formats don't take hold. Only when I hit F2 for each cell does the change take place. It's really annoying when I'm pasting thousands of cells. Please help! Thanks!
 

Smitty

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

You can apply the format you want, then select the range and goto Data-->Text to Columns-->Finish. That will apply it to all of the cells at once.

Hope that helps,
 

ladyfear

New Member
Joined
Oct 6, 2008
Messages
5
Thanks you for the welcomes, and for the help, I really appreciate it. And thanks for the tip. Curious, is this just an Excel quirk, or is there a permanent fix? It happens, too, when I just attempt to format a row/column. I don't mind the text to columns, but again, annoying that I have to go through an extra step just to get my data to format the way I want it to.

Also, I have noticed that in the past for some of my spreadsheets, the whole workbook won't update unless I hit F2 in the cells which need to update. Example: A cell on Sheet 1 which is linked to a formula on Sheet 2 will not update even if the formula or corresponding data has been updated, and not even if I hit the "Calculate Now" button in options. Seems like a similar problem.
 

therios

New Member
Joined
Sep 30, 2008
Messages
37
For example, let's say I'm copying a column of numbers from WB1 to WB2. The original values are derived from formulas, formatted in "Comma". When I paste special/values to WB2, I expect the values to be formatted in the formats set in the new sheet, say, "Accounting." But for whatever reason, the "Comma" format is prevalent. When I manually try to format the pasted data in WB2, the new formats don't take hold. Only when I hit F2 for each cell does the change take place. It's really annoying when I'm pasting thousands of cells. Please help! Thanks!
When you choose to copy the values only then that is all that you are going to get. Maybe try Edit --> Pase Special --> select "values and number formats" and try that.
 

ladyfear

New Member
Joined
Oct 6, 2008
Messages
5
Thanks for the reply, Therios. Unfortunately, it's not the formats that I want to copy over, it's just the data/values.
 

Forum statistics

Threads
1,082,044
Messages
5,362,843
Members
400,694
Latest member
Sofie17

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top