Solved

Excel Table Formatting new rows

Posted on 2015-01-17
11
58 Views
Last Modified: 2015-01-29
I have created a table from some data.  There may have been other data and formats used on the workbook.  I have added to the table as well as structured references.  When adding rows, the structured references come up fine, however, some of the cell formatting for the cell does not follow the previous row. (Like number format).  I have tried clearing all formatting and deleting rows except for the first row and still the same issue.
0
Comment
Question by:ekaplan323
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40555725
Can you attach your workbook?
0
 

Author Comment

by:ekaplan323
ID: 40555860
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40555885
When I add a row I just select the row, right-click, and choose 'Insert'. When I do that the fields seem to be OK. If that's the way you do it then please give me the details of the problem like what cells are formatted wrong. If you don't use Insert, how do you add a line?
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:ekaplan323
ID: 40556205
When you tab through the line to create a new row, or even more importantly when you copy and paste just values from another area into the table.  Lots of times there is data from another source that can be just pasted to add to the table.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40556260
When you paste, use Paste Special|Values.
Paste ValuesScreenshot-1-18-15--7-55-AM.jpg
0
 

Author Comment

by:ekaplan323
ID: 40556305
I did that when I pasted.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40556337
Sorry to ask you to do this again, but could you possibly create a workbook with two tables that are the same except that the second contains extra pasted data, with the data that was copied and pasted being from some cells someplace in the sheet.
0
 

Author Comment

by:ekaplan323
ID: 40556705
I pasted the data from Sheet 1 to the second table below the first.  I copied the first table to the second table below.  As you see, the formatting did not happen.
Table-Cell-Format-Issue.xlsm
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40556778
Try this. Go to the Visual Basic environment, insert a module, add this macro to the module and assign a shortcut key (I used "t") to it. If you need help with any of that please let me know.

Then copy whatever rows you want, select a destination cell and type ctrl+t.

Sub CopyTableRows()

    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

Open in new window

0
 

Accepted Solution

by:
ekaplan323 earned 0 total points
ID: 40568154
In experimenting with the tables, I found there must have been formatting of the cells in the worksheet.  I highlighted the entire rows of the affected data.  I cleared all the formats form the Home Tab on the ribbon. Then formatted by table cells with the format desired.  After that when I added new lines, the formatting was correct.
0
 

Author Closing Comment

by:ekaplan323
ID: 40577053
Table formatting of cells  are supposed to continue as you add rows.  This solution worked for me.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question