Need to add code to add an extra sheet with condition

Hello Experts
I have this application which combined the data of 3 sheets from source sheet, after some conditions are fulfilled in a new workbook. This time I received a data which has an additional sheet (cdnur) in the source file. I need your expertise to help me to add the data of 4th sheet in the destination sheet last empty row with some conditions. I am sharing the expected result in the file for reference.
Please note that there are 2 sheets shared one is the source which should be closed when running the macro and the other where the button is to play the code.
Thank you in advance.
Sorry forgot to change the amounts to negative in the sample expected sheet. The figures to be in negative.

Yet again, a lack of explanattion:

with some conditions

So what are they ?

Sheet "cdnur" column A has data "EXPWOP" but in "Combined Invoices" w/book it is (or appears to be) "URP" .etc

Originally Posted by JohnTopley

Yet again, a lack of explanattion:

So what are they ?

Sheet "cdnur" column A has data "EXPWOP" but in "Combined Invoices" w/book it is (or appears to be) "URP" .etc

I want URP in place of EXPWOP.I tried editing the code but was not getting it right. Please edit the same code as it is easy to understand. I have missed something which I am not able to understand. See the edited code I tried.

Please Login or Register  to view this content.

Removed by JT

And yet again...Another 40 post thread coming up...

I have corrected one mistake in the above code. I have replaced "=cdnr" at all places with "=cdnur" but still not getting the expected result.

Not funny sintek. I have threads which were solved in 2 or 3 posts only.

After further deleting, I got the display in the required columns except for column I and one more thing is missing as the cdnr rows are overwriting the sxpo rows. I removed the lines of a few columns showing blank which were not important for now.

Please Login or Register  to view this content.

Further confused as code is in "Combined...." yet data sheets referenced by the code are in "EINV..."

If data is being overwritten it suggest the "lastrow" code is wrong or missing.

Please Login or Register  to view this content.

This line is printing URP but overwriting the exp rows. That is what I am wondering what I missed to avoid overwriting.

Use Debug.print to ascertain the various row counts (Lastrow) values.

Got most of the columns right without overwriting but I am getting a value error in column I of URP rows. This is the only issue pending to be solved. I have attached an image of the result.

Please Login or Register  to view this content.

Attachment 827604

Cannot view attachments!

Looking at sheet "ws_b2b_sez_de" column I is TEXT ("Invoice Type") for perhaps it should be column L ("Taxable Value") or from L to ?

Please Login or Register  to view this content.

Column L in "ws_b2b_sez_de" is Invoice type and ws_cdnur column D is the same (Note Type). It is C in column D which has to be entered as Credit Note in column L of "ws_b2b_sez_de" sheet.

In workbook "EINV ...." sheet "b2b, sez, de" column I is "Invoice Type": you miss the point that the error is in the calculation I posted in the last post.

Another example here of crossed swords as we appear to be referring to different w/books!!!!

Originally Posted by JohnTopley

Cannot view attachments!

Looking at sheet "ws_b2b_sez_de" column I is TEXT ("Invoice Type") for perhaps it should be column L ("Taxable Value") or from L to ?

Please Login or Register  to view this content.

Looking into it. I think you are right.

Clumsy of me. I got it right now. You can tale back your sword frpm my neck now.😜
Thanks John Topley. It is solved now. ").

In future, just spend a little more time investigating your problem(s) before putting fingers to keyboard Testing as as much a skill as coding.

Originally Posted by sintek

And yet again...Another 40 post thread coming up...

Solved in less than half of 40 posts. 😜 After viewing JohnnyL's comments it took 5 hours to edit this one code. You should understand I am a bit slow at understanding.

It is not about your understanding...it is about your explanation of expected output...You always post code and ask to have it corrected instead of posting explanations of how your result was achieved...
It's sometimes a nightmare to try and analyse another persons code...or results based on their supplied formulas...

For future reference: consider having a macro per sheet i.e code to process that sheet only. You will probably find that much of the code can be copied/pasted with minor alterations to each macro: particularly with the type of application you are working on..

Re JohnnyL's style (and we all have our "favoured" approach/style) of using"comprehensible" (self-documenting) variable times _ in a large macro I start to find them difficult to follow.

In simple one-per-sheet a more "simple" naming approach could be used without losing the documentation, much of which can be provide by comments (as you have, understandably, requested).

Good design usually leads to both simpler, and more efficient, code. Excel itself is composed to 1000s of small reusable "macros" which are used iin multiple situations.

I just read comments of previous codes and try to create something what is required by comparing codes not writing them. When I get stuck at a point I ask for help in the forum. I even post an expected result sheet and the result I have achieved till then. However, with the members support, I may not be able to explain clearly but someone or the other understands and my goal is achieved. I am sorry if my post gets you confused but you can ask and I can explain one step at a time. Only thing needed is patience and the will to understand to solve which everyone in the forum has and I am very glad about it.

I am trying my best John Topley. Improving by the day. This query was connected to multiple workbooks so I had to post 2 sample workbooks. Whatever, you understood and solved the problem. Thank you man.
Time to hit the sack now. Good night guys. See you soon.

...Deleted...

sintek. It is not that simplle. Share the workbooks with the code you tested and let me check.

Must only credit note values be changed to negative...

I can guide you to recreate (play with) it but then again the posts may cross #40 plus. (Just joking).
First thing is it is not a single xlsx sheet. There may be one or up to 30 sheets in different scenarios. That is why the array is created. The code will collect the data from the first xlsx sheet and join the data from the four sheets with some conditions. The condition includes that out of the 4 sheets 3 sheets may or may not contain data. Next it will do the same with the next sheet in the folder and place the result below the result of the first sheet and so on till there are as many xlsx sheets in the folder. The code will create the sheet name depending on the name of the xlsx sheet. If you understood this I will explain further if you want. It took more than a week to create this with help.

Originally Posted by sintek

Must only credit note values be changed to negative...

Yes. The cdnr and cdnur sheets values should be in negative.

Oh okay...not to worry...I misunderstood...Wrote code only for that Invoice File extracting various columns for array based on only this sheets setup...

Untitled.png

If all the files had the same setup as that invoice file then adding a loop would work...

No worries. You had fun that counts. It is really exciting to do something new each time once you know what is to be done.

The invoice file is the copy of b2b,sez,de sheet and then the data, if any, in cdnr, cdnur and exp is copied from each selected column and pasted in the required column of b2b,sez,de sheet.

That code of yours is doing exactly the same thing all the time with every file it opens, so the data layout has to be the same...

The time taken to combine 25 sheets in this case is
A_Combine Data From EInvoices completed in 8.9951171875 seconds.
A2 Combined E Invoices sheets in 0.6220703125 seconds.
What more can I expect.

All good...