?
Solved

Excel Table Formatting new rows

Posted on 2015-01-17
11
Medium Priority
?
60 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 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