Inserting multiple rows into a table while maintaining data?

Please trust me, this is a relatively simple question, but takes some explaining:

I want to insert multiple rows (anywhere from 60 to 900+) into a table that I routinely use off a template. Every time I open the template, I have all my appropriate column headings, and I paste data from another program into this Excel sheet. My desired outcome is that I am able to paste rows of varying numbers, while still maintaining the last rows (for sum totals of each column) on my template. (This would help automating the task)

For example, I start my column headings in row 6. A6, B6, C6, and D6 all have headings. I have cells reading "Total" in A8, B8, C8, and D8, and the SUM result would be displayed in row 9. I want to insert lots of rows between the two, while still maintaining my "total" columns (In this way, I can make a macro to make this once-a-week task more efficient).

The problem is that if I highlight A7 (first blank cell) and Paste the rows, it simply overwrites any data that occupies row 8. I want to push these cells down instead of overwriting them. Because my inserted rows are of varying lengths, I cannot anticipate where the bottom of the table will be. Functions such as "Insert" or "Paste Special" do not address this issue.

It seems to me that there must be a way to insert multiple rows into a pre-existing table without destroying the original data. Imagine pasting multiple rows into the middle of an invoice- you don't want to lose the other data, but you need to put an important item (series of rows) in the middle. Is there some way to do this, preferably without using VB?

I tried to replicate your problem. All I did was copy the data, clicked in a blank cell and right clicked 'insert copied cells', then when the 'insert paste' dialogue box appeared I clicked in the 'Shift cells down' check box. Worked fine for me.
hth, Chris

Ah- I knew there was something I was missing. This is a 3rd party software issue. The problem I'm encountering (which I could not articulate until now) is that I am copying cells from another database program (not excel or word), and excel must not recognize them immediately as "copied cells". I can paste these cells normally just fine, but when it comes to the extended options that copied excel cells have, I think I'm out of luck.

Perhaps at present my best solution is to copy the cells to another excel sheet, and then recopy them once in excel, thus giving me the ability to insert them as copied cells. Of course, this results in less of the automation I was desiring, so I think I will have to be creative in a macro.

Thanks!



"Chris from Waterworks" wrote:

I tried to replicate your problem. All I did was copy the data, clicked in a blank cell and right clicked 'insert copied cells', then when the 'insert paste' dialogue box appeared I clicked in the 'Shift cells down' check box. Worked fine for me.
hth, Chris