Select and copying a variably sized range and add data to a table

[Masun]

Hi!

I have 2 sheets, one is a table (you know, when you push the button the rows get different colors and autofilter is placed in the top.), I have that table as a data source to some pivot tables (in the file im uploading this sheet is named "DataTabell"). The other sheet is some kind of raw data, that is "Utdata".

I got a lot of help with the code that takes data from sheet "Utdata" and places it in the first empty row in sheet "Datatable". This works perfectly, the macro is constructed so it always takes data in sheet Utdata from A4 : ??. I dont know how many rows or columns there are from time to time in the sheet "Utdata". This macro handles the data correctly:

Please Login or Register  to view this content.

It probably should be mentioned that the data in "Utdata" always has some "rubbish" in the end of it. Look att row 576 in sheet "Utdata", there the rubbish starts. In the file uploaded, the range that needs to be moved to sheet "DataTabell" is A4:AH573, but his can, as earlier sad, vary from time to time.

The problem now is that the data that is being taken from sheet "Utdata" and placed in first empty row in sheet "DataTabell" is not being automatically added to the table in sheet "DataTabell". Usually when you have a table and put something in the first empty row below it, its being part of the table automatically. Now the data is added in the correct place but it is not added so it becomes part of the table. First 199 rows in sheet "DataTabell" is a table, if I run the macro the data from sheet "Utdata" is placed beginning on row 200 but will not be apart of the table...

Hope someone can help me and that I made myself understood, if not; please ask again and I will try to explain more.

/Masun

[AB33]

Masun,
The reason the data are not added to table is that the table has a fixed size 1-199, you need to increase the size of table. You do not need the macro to do that. Go to table, then resize table.
As I said before, you code will not function if you have a blank row. The code works as you just happen to have not blank rows before row 573. The code also does not copy the rubbish because there are blank rows just before the rubbish

[Masun]

AB33,

I understand that I can resize the table manually. I would like to avoid that (because I have the table as a data source from whice I make a couple of pivot tables and I dont want the users to have to do this by them self) and I need help to get the table to resize automatically. Otherwise if you have a table in a random excelfile and adds data in the first empty row below the table excel automatically makes it a part of the table. I would like this to work in the same way.

Regarding the code; There will never be any empty rows except those two between the "good old data" and the rubbish. I dont want the rubbish to be copied so the code works as I want it to. The problem is when placed below the table it doesnt become part of the table.

/Masun

[AB33]

Masun,
Okay, forget about the code for a minute. What happens if you try to type any word in row 200, will this word auto be part of a table? No as far as I know. I have not used a table for years now, but if you want to make sure that your new data to be inclduded, you need to re-size your table. I may be wrong and do not know how to write a code to resize a table.

[Masun]

AB33,

Thanks for your effort. If i write "sdfsdfwe4rwefrwefr" in A200 in the uploaded file the table automaticallys includes row 200. That´s what I want with the copied range but when that range is placed in the first empty row, in this example row 200, it does not become included per auto in the table...

[AB33]

Masun
Okay, I see your point,
I have tested the code by changing the last line in to

Please Login or Register  to view this content.

and run it. It copied all cells starting from row 200 and table has adjusted it self, so the table size is 769. Test it yourself and see if I could amend your code.

[Masun]

AB33,

Somehow the data was included in the table. But when running the macro twice it places the data wrong, the data is put in on row 200 even the second time and that will, unfortunately, not work.

Maybe I am trying to solve this from a strange angle. Maybe i could give the table a name and somehow try to resize it...