VBA Invoicenumber in "invoice"-sheet and overwrite if exist in "invoicedabase"-sheet

[pa3ewc]

Hello excelforum users,

I have following issue:

I make an example of an "invoice"-sheet. ONLY the cells what I want in there are been copied to my "invoicedatabase"-sheet.


2.jpg
5.jpg

I found an vba code to copy all the cells what I want from this "invoice"-sheet to my "invoicedatabase"-sheet. So far so good. This works...

But I don't know how to arranged that if I changed an allready made invoice (with my invoicenumber in cell H5) that it will copie this to my "invoicedatabase"-sheet in column A.?

So what I want that the vba code must do is the following:

- if this invoicenumber exist in this "invoicedatabase"-sheet....it search for this invoicenumber and overwrite allways the cells with the changes what is
made in my just changed "invoice"-sheet.

- after the cells are copied make sure that the invoicenumbers in the "invoicedatabase"-sheet will sort automatically the invoicenumbers (kolom A) and
the last invoice what is made stays allways at the top of the "invoicedatabase"-sheet. (sorting z to a)

e.g.: 2019DK124 (last made invoice)
2019DK123
2019DK122
2019DK121
2019DK120
etc...

Hopefully someone understand what I want to happen and can help me.

[mjr veverka]

A very nice combination of Excel and Paint, or maybe is it a new version of "PhotoExcelShop" in the "LabGIMPView" environment with the "CorelAutoPhotoCadScape" GUI ?

[pa3ewc]

Hello porucha vevrku,

Maybe my info was to much to understand,hihi.

I will try to make it simpel that everyone can understand what I want to happen.


What I want that the macro must do is that in my "invoice-sheet" some cells I choose will be copied in my "invoicedatabase"-sheet. ( look for my 2e picture )

There you can see the "invoice-sheet" with some data in the cells. These cells will be copied in a row in my "invoicedatabase"-sheet per invoicenumber with the weeknumbers and the worked hours.

That's what I want that my macro must do.! And that is what my macro does...so that's ok so far.


What I also want that my macro must do is the following:

After the cells are copied in a row per invoicenumber ( look in my 1st picture for the macrocode I have sofar that works fine )
it must do the following:

1. Always sort the last made invoice in top of my "invoicedatabase"-sheet.

2. When there is allready an invoice made with the same invoicenumber (sometimes you have to correct an earlier made invoice) it must look if this
invoicenumber exist in my "invoicedatabase"-sheet, and automatically overwrite this row with all the new changed invoice what I just corrected without
asking if I want to overwrite it.

3. It must always sort it automatically after I copy an new invoice. (newest allways on top of the sheet)


Hopefully now it is easy to understand and maybe you can help me with this. Thanks for reading.

Best Regards, Andre

[dotchiejack]

What porucha vevrku means is post a small resembling example of your file (without sensitive info) in which you can clearly see what you wish to achieve.
Pictures belong in a photoshop forum, not in an Excel forum.
of in het Nederlands:
Wat porucha vevrku bedoeld is post een klein gelijkend voorbeeld van uw bestand (zonder gevoelige info) waarin duidelijk te zien is wat je wenst te bereiken.
Plaatjes horen in een photoshop forum, niet in een Excel forum.