Transfer from one table to another auto sorting table

[Noobulon]

Hello guys, im looking for a macro that would help to generate the table alone as i add new entries.

Here is the table that is going to hold all the information :

TABLE1111.PNG

A score is gathered every weekday as the dates on the header show (25-Jun-17 is weekend), it would be nice that the dates could automatically add as I populate the table.
As this table will have over 1000 addresses and roughly 300 days a year.

Here is two options for my Data Entry Tables :

TABLE22222.PNGTABLE33333.PNG

The data in the orange table would be entered manually each day that the scores are collected, I'm looking for code for the "Add Data To Blue Table" button that would transfer all that data from the orange table to the blue one.
Now,
- if the address is already in the blue table I would like the score to be added to the its right in the proper date column.
- if the address is NOT in the blue table, the Macro would create a new column for the address prior to adding its score in the correct date column.

Any tips and advice is greatly appreciated !!

Thanks for your time,

Jeremie

Ignore Bottom Attached File

[danielexcelvba]

You can give this a try (you will need to change the worksheet names - I used sheet1 for the sheet containing the orange data table and sheet2 for the sheet containing the final blue table).

Please Login or Register  to view this content.

Edit - should probably have mentioned that this is for the first orange data table setup
Edit #2 - this assumes you already have all the dates written in row 5 of your data table. If you need the code to populate that row it will have to be edited

[Noobulon]

Hey danielexcelvba,

Thank you for responding

There seems to be a error in the code " Run-time Error '91': Object variable or With block variable not set " when I run it.

It adds only one instant of the new area in question (Petico,56, Sali) then (Petico,57, Sali) the second time I run the code (after stopping debugger and running again).

Then I run it a third time for (petico , 58 , Sali).

After that, once there is no addresses in the orange table that the blue one doesn't have, the code runs without any errors, but doesn't seem to be transferring the 'SCORE' .

Here are the Results

TABLE22222.PNGTABLE1111.PNG

The score doesn't seem to be added to the new or the old dates, I will be looking at it and try to figure it out, I will update as i go.

Thanks

Jeremie

Forgot to say where the error was ..

Please Login or Register  to view this content.

[danielexcelvba]

Hmm would it be possible for you to attach a desensitized workbook so i could try it out? It's working on my sample data.

[danielexcelvba]

Without seeing the workbook, the only thing I could think of is maybe you don't have 28-jul-17 in your blue table? Is it possible it's supposed to be 28-jun-17 (based on your screenshots)?

[Noobulon]

Sure that would be great!

[danielexcelvba]

Ah yes, this is why I asked earlier if you had all the dates in row 5 of your blue workbook. This code is searching the blue table for the date found in the orange table. If you don't have all the dates written out, I'll have to edit it so that it fills in the date heading

[Noobulon]

And no I do not have that date yet, i was thinking it would create a column automatically, im guessing i can just expand it by hand

[Noobulon]

Ohhh ok, i understand what you mean now!!

[danielexcelvba]

I think this should solve the problem

Please Login or Register  to view this content.

[Noobulon]

HUGE thanks Daniel, it works like a charm.

[danielexcelvba]

You're welcome If you don't have any other questions please mark the thread as solved (in thread tools)