Solved

Excel Table Formatting new rows

Posted on 2015-01-17
11
59 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 48

Expert Comment

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

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
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!

 

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 48

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 48

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 48

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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‚Ķ

690 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