Here's a little riddle - Pasted rows and determination of range (Autofill in table)

[Mahisse]

Hello... I'm hoping this post will be understandable....

I have a problem regarding pasting rows from one workbook to another, where I want the pasted row to be become a part of a table with autofilled formulas in the end of the table.

However, I have stumpled upon a problem, which I cannot narrow down to a specific root cause.

Let's say the range of the row I want to paste to another workbook has data from column A to AB (A:AB).

Normally you can just select the entire row by clicking the row number in the worksheet and press crtl+c, then select the first column cell in the new workbook and paste with ctrl+v. But here is the weird part...
In some cases pasting in an entire row seems to have no range limit, thus highlighting endless cells in the pasted row in the new workbook. Because of this the table, which are meant to do autofilling does not recognize the pasted row as same format as the other rows in the table, causing the table not to expand and autofill for the new row.

In other cases I can easily mark the entire row and paste it into the new workbook, where the row will have the same range as from where I copied it (A:AB). I can easily see this because the highlighted cells that were pasted are limited to the range (A:AB), whereas in the other scenarie the cells will be highlighted with no range limit (A:....n).

I played around with this a bit in different file formats but the results can vary from time to time, not providing me any specific clues to go after. Sometimes I paste in a row with endless cells.. Sometimes I paste in a row with the original range, as intended.

So I guess my question is if there is anyone, who knows when Excel thinks it should copy a specific range of a row and when it thinks it should copy a limitless range of cell with no data?

I would really like to avoid having to specify the exact range of copied cells each time I need to copy a row. I hope someone can help me with a good explanation of this.

I also tried several times to delete any "empty data cells" after the range of a the copied row (ctrl + ->, delete) to test if Excel thinks there are more cell after the last cell with data but this doesn't seem to have any impact..

Please help

 

[JLGWhiz]

The range copied must be equal to or less that the receiving range, with respect to the boudarys of the receiving range. In other words, you cannot paste data into an area that does not exist. If you copy an entire row or entire column and do not use a cell in Column 1 or Row 1 as the anchor cell for the destination, then the bottom of the column or end of the row will not be able to paste and it will throw an error. If you copy an entire row and then try to paste it into a defined table, it is the same effect. The receiving range is less than the the source range and cannot paste the data into that smaller range. Don't be confused by the visible data that you see when coping an entire row. It may only have ten columns that show data, but Excel sees all of the cells in that row as range objects even if they are blank and it will expect that same number of cells to be available if you try to paste it, If they are not, you get the error. To avoid the errors, select only the number of cells that you can fit into the destination space for copying and pasting.