Pasting into a List

[NicolaasH]

Hi,
I have a List (Listobject) in a worksheet, to which I wish to copy new data. Unfortunately it does not seem to be possible to paste the data when the number of rows in the copied data is larger than the number of rows in the existing list, or when trying to add a range of rows to the bottom of the list at once.
It seems like I have to manually expand the list to enable this. Unfortunatly this is not very user friendly.
- Is there a way to overcome this difficulty? How would I do this?
- or would I have to refer to VBA to solve this? (i.e. write a method that counts the number of rows in the copied data, add the required number of rows to the list and then paste the data)

Thanks!
Nick

 

[AnotherHiggins]

only select the top-left most cell before pasting.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[NicolaasH]

I did that, (tried several things: top left cell, top row, insert row, column name row) nothing like that seems to work.

 

[AnotherHiggins]

let me transpose two words to try to make that more clear:

select ONLY the top-left most cell before pasting.

You'll only get the You'll only get the message about the ranges not being the same size when you have more than one cell selected.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[NicolaasH]

I understand what you mean and I did just that- i.e. select the single top most cell of the list.
When pasting it actually outlines (cells become selected/blue) where the new data should have come. It does not give an error message when pasting into the topmost single cell. There is an error message when trying to add multiple rows to the list at the bottom.

 

[NicolaasH]

With some experimentation, I have found out that the problem lies in the fact that the list into which I paste contains a couple of extra columns. These columns contain formulas (date conversion, matching etc.) and I would like to keep them in there.

Do you know whether there is a solution to my copy-paste problem, considering the above?

thanks!
Nick

 

[SkipVought]

Paste the new data.

Copy 'n' paste the formulas.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NicolaasH]

I believe I did mention that the pasting didn't work....It keeps on saying 'Select destination and press ENTER or choose paste' at the bottom of the screen, but neither does the job.

 

[SkipVought]

Exactly what did you COPY that would not PASTE?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NicolaasH]

1) I have a download from an ERP system into Excel. It contains 23 columns. The number of rows varies, since more data is acquired over time.

2) I have an Excel workbook that contains a whole lot of data and worksheets where I manipulate this data. I have a separate worksheet to enter the data from (1). In this Excel sheet the data is in a list, so when the amount of data changes my corresponding pivot tables keep referring to the whole list. I have also added 4 columns with formulas (so this list is 23+4=27 columns wide) for further data manipulation.

I want to paste (1) into (2). I wish to copy all the data in (1) to paste it either over or under the data stored in (2). This doesn't work if the number of rows in (2) is less than in (1) which is usually the case.

 

[SkipVought]

Frankly, I rarely use copy 'n' paste unless doing some ad hoc quicky thing. I most often construct lists, from data in other sources, using MS Query faq68-5829. The entire list is reconstructed each time.









Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NicolaasH]

Unfortunately that is going to be too complicated for the people that will be using the sheet. I think I'll go for the VBA solution, although I might have some questions about that too.
Thanks anyway!
Nick

 

[GlennUK]

I want to paste (1) into (2). I wish to copy all the data in (1) to paste it either over or under the data stored in (2). This doesn't work if the number of rows in (2) is less than in (1) which is usually the case.

So, if you are copying over the data stored in (2), then copy the entire columns.

If you are copying under the data, then selecting a target of a single cell should work perfectly well.


Cheers, Glenn.

Beauty is in the eye of the beerholder.