Update worksheet from source with VBA, but not updating

[sknalodz]

Dear All,

hope you are doing fine.

i am having a big worksheet holding parts.
from time to time i need to make an update.
for last years i was doing it manually, which took me approx. 3-4h.

this year i decided to write macro which will import only NEW parts from the source worksheet.

i looked over Internet, found code using Find method.
but it does not work. in theory it should be very simple code.

what i expect from the code:
- initially code checks if the part exists already in the DataBase Parts
- if not, then should be added at the end of the DataBase Parts from the source worksheet.

below code does not update the content, beside that returns the error "Object Variable or with block variable not set".

can you please help me here? thanks in advance.

Please Login or Register  to view this content.

[AlphaFrog]

Please Login or Register  to view this content.

[sknalodz]

Hi AlphaFrog,

thank you for quick feedback.

I don't know why but your code returns value from last row only...
even i cleaned completely the DataBase Parts so it is empty and all rows from Source should be copied...

do you have idea?

btw. maybe i should add one info, wsS.Cells(n, 4) holds either a six digit code i.e. 378432 or a string R44-1011-01.

[bakerman2]

You both forgot the underscore in the variable name Last_RowD.

Please Login or Register  to view this content.

But a loop like this will take a long time on a large worksheet.

Try this instead.(took 0.2 sec for 5000 lines)

Please Login or Register  to view this content.

[sknalodz]

Dear bakerman2,

thanks. yes it is extremely fast macro.
looking for your code i have to say that i am far from VBA...

got one question, the source beside the unique code, holds also parameters of the part.
how to use your code to get other parameters to the destination worksheet?

i.e.


Parameters Source Destination
Length Column E Column B
having approx. 70 different parameters describing the unique part.

optionally i can use match and index via formulas but it will be slow like hell.

thanks for the hint.

[bakerman2]

Can you post an example workbook with Source & Destination sheet, including headers and a few parts so as we can see which value from Source

has to be copied to which column on Destination.

We could then create Array in memory and write to Destination sheet in 1 go.

[sknalodz]

please find the file.

the problem i see (or maybe it is not a problem) there is no same order in both worksheets.
and this cannot be changed...

thank you for your time!

[bakerman2]

Based on your example file.

Please Login or Register  to view this content.

[sknalodz]

Dear bakerman2,

thank you. it works really fast and in the way i expected.

i did not know it before. i will try to understand it...


i owe you a beer or should sponsor a coffee

have a good day.

[bakerman2]

Let's stick with the coffee.

You're very welcome and thanks for rep+.

If you have any questions regarding the code just give a shout.