Adding New Rows to a table from another table

Originally Posted by rpjwhite

....So I have 1 large table which is combining 10 other tables on different worksheets into one. I have the code to combine the sheets and it works just fine (see below) but I want to make it more clever (poor English). I want it to check the sub tables for new items and add them to the main table. It would need to check all columns to confirm the item is new before copying them over. .....

Hi Robin.
I think I catch the general drift of what you are asking.

I highly expect someone has done something similar before, but I do not think you can effectively search for that sort of thing. If you have a month free then do a Google search with variations of “vba merge worksheet data based on criteria” then expect a thousand links, one of which might coincidentally do exactly what you want.

I have been thinking myself about copying and / or selecting data depending on whether it is “new”
The obvious “unclever” way would just be to mark all cells , possibly with some Formatting , after every “update” of your master you do. Then next time around the unmarked Cells would be copied. ( That could be advantageous of course for you to see what is old data ).

I think if you are fairly happy with the code you are using now, then you know probably what you will have to do for your new requirement: You will need at every Worksheet to go through the columns and copy just the new data, then adding that on to the corresponding Master Worksheet columns. I expect you may have an idea how to do that?.

As for “Clever” stuff
That will depend a lot on your actual data type and layout. I expect the “clever” things might involve using the VBA Special Cells Method to get quickly at new entries in a column.

But these things will be using so called ( by me anyway ) “spreadsheet interaction techniques”. Whether they are in end effect “clever” or just look “clever” will depend on your data type and layout. Sometimes if you use VBA “Array techniques” that look more boring they may be more efficient.

I can think of some “semi clever” ideas for part of the problem.

Like, pseudo, for one column, if you have a start situation of this_.....

Using Excel 2007 32 bit

Row\Col	A
1	OldData1
2	NewData1
3	NewData2
4	OldData2
5	OldData3
6	NewData3

Worksheet: Sheet1

Row\Col	A
1	ExistingData1
2	ExistingData2
3	ExistingData3

Worksheet: SummarySheet

_....then in a fairly “clever way” I could copy the new data and paste it into the Summary sheet for each column in one go such that you get_....

Row\Col	A
1	ExistingData1
2	ExistingData2
3	ExistingData3
4	NewData1
5	NewData2
6	NewData3

Worksheet: SummarySheet

_..... then in a fairly “clever” way I could “mark” all the Cells in “Sheet1” such that they "look" to VBA as not new data.

Basically I am thinking of messing about with the data such that to us it “looks” to us in exactly the same format regardless of if it is new or old data. But to VBA it will be able to distinguish between the two.
I am thinking of messing about with whether a number “looks” to VBA as a number or text and / or similarly messing about such that an entry might "look" to VBA as either a formula or a value.
We would see everything the same, but VBA could distinguish through the VBA special Cells Method as it would see the data Type as different.

But once again the exact implementation will depend on your data type and layout

If you want more help we will probably need to see a bit of sample data.
Here some notes on that:
http://www.excelforum.com/showthread...42#post4532042

I will probably be mucking about with my data in a day or so. So if in the meantime you get some test data across and you or no one else sorts it out for you, then I can take as look at your stuff when I look at mine.

Alan