Command Button - VBA To Insert Table Row not copying Table Format for Percent

The attached sample is from a much larger workbook that includes multiple tables and a user form to populate the main database. I have inserted a table on the dashboard with a command button to insert a row. The VBA code is simple enough and a row is properly added when executed. The problem is, the column with a percentage format is being copy with a $ format instead of a % format. I copied the range to a table and reinput the formulas and formats in the first row and reconverted it to a table, but the issue persists.

Hi Again Torachan

Sorry my original attachment was not saved as macro enabled. I tried your suggestion before I posted the thread and again after your response to make sure. It's still changing the percent format to dollars. All other formats including my borders and the dollar and bold formatting on the COST column works fine. The attached sample is macro enabled.

That is really odd. It does not work for me no matter what I do. I can't format the entire column because it is part of a larger dashboard. In both my main project and this example, it will not retain the percent format.

In the example and my main project, I tried again to convert it back to a range to reset the formulas and formats and then re-converted it back to a range. I also formatted the entire column in my example. Still no dice. I inserted a table row manually with the same results. This is a huge mystery as to why you have success and I don't. I hope I'm not forced to write unnecessary code. Actually, I'll probably abandon the command button before Igo through all that work and train my users to insert a table row and copy the formatting down.

Hi Torachan,

I exhausted all the advice I could find to no avail. I took a different route to solve my problem. VBA. I simply named the errant range within my table and instructed it to format the range as percent and viola, my problem is solved. Thanks for your help!!

I had a similar issue with my main data table though, where the alignment on 2 of 40 fields in a row would not copy down. I followed your advice on those columns of data and the alignment formatting copies down correctly.

I guess the issue with the first table will remain a mystery.

Ken

[SOLVED]