Distribution of values

I have this code that merges two data sets exactly how I want it. This part works perfectly thanks to. I have also the second part of the code which distributes the results of the first part into new worksheets…

I had this working for a while, but then the first part of the code got modified and now the second part of the code is not working. When running the code.

Here is the first part of the code that merges two datasets:

Please Login or Register  to view this content.

And here is the second part where the result of the first part is distributed… which gives error:

Please Login or Register  to view this content.

See attachment for more details.

Hi Arkadi,
Here is an old version where the distribution sub worked together with MergeAndReport sub (attachment).
I am still researching how the MergeAndReport sub works, since my VBA knowledge is not that advanced. The person that helped me build the MergeAndReport sub did a wonderful job, but forgot to write some comments. Upcoming days I will try to understand the code...

Having said this all, I am not able to answer your question Arkadi, since I don't know how the MergeAndReport sub works and how it communicates with the distribution.

Hi Arkadi,

I really appreciate it that you are taking time to help me with this.

Here some background story:
Jindon is the person that helped me allot, creating the solution. Jindon and I managed to create a solution that gave me the exact output (column L:R). Last week when I implemented the solution in the main reporting tool the users experienced a bug. This bug is described here and jindon again helped me by modifying the code and we managed to get the right output, how I wanted., thanks to jindon.

The problem I have is that the distribution code, which comes after the merging code, is now not communicating with each other.

In the ReportingTool_v4.4_old.xlsm‎ attachment, you can see a fully functioning tool. But this one has this bug that I described here. The modified code is also provided in the other thread which I implemented in this file: ReportingTool_v4.4.xlsx.xlsm‎.

Hopefully I answered your question Arkadi

Thanks Arkadi, take your time

It has been quite a nice journey for me, my VBA knowledge has been tested well with this project and as result, i fell again in love with Excel and its endless possibilities.

Please Login or Register  to view this content.

I have no words...jindon
I am now trying to implement it to into the ReportingTool_v4.4_old.xlsm‎ and have some issues but I will figure it out.
the moment is figure it out, I will let you all know

Thanks again jindon

I guess duo to chain reaction, the whole reporting tool stopped working. Last night and today I had my part of sweating trying to fix it all, but unfortunately I lost.

To make it clear for myself and have an overview I will try to explain the current state (I know it’s long, but please bear with me, I’m in desperate need of advice):

I have the following subs in my reporting tool:
1 - Sub Merge (jindon calls this test)
2 - Sub Distribution
3 - Sub ButtonGenerate(SheetName)
4 - Sub SaveShtsAsBook()

The problem started with the merge sub. I founded a ‘error’ and jindon fixed it. Then I tried to patch the subs all together when I discovered that the merge sub works now differently and thus I was not able to easily patch the sub merge together with the sub distribution. Again jindon came to the rescue andfixed this problem (see this thread).
Last night and this morning I am trying to patch sub distribution with sub ButtonGenerate(SheetName). Again here, I am lost because the sub Distribution is different now and thus I am not able to connect (passing the variable to sub ButtonGenerate(SheetName)). I tried many ways to pass the value from distribution to the ButtonGenerate(SheetName), but failed. I also troubleshooted with my colleagues and also they did not managed to get this fixed and that's why I am here again

Here is an overview how the old process of reporting was working, before I discovered the error I discovered it.

The old procedure went as followed:
1 - The two data sets were merged in the worksheet Merge
2 - Than a copy was made from the Cal_template worksheet and pasted and renamed to a person from the Merge worksheet
3 - Than the data of that person is copied from Merge worksheet
4 - The next step is copying formula’s into column E
5 - Finally the ButtonGenerate sub is applied on that worksheet
6 - And the loop starts again by step 2.

The new code jindon provided works differently and I wonder what the reason is for that? Perhaps it’s more efficient? The new code works as follows:
1 - The two data sets merged in the worksheet Merge
2 - Than the data of the first person is copied from Merge worksheet to a new worksheet and renamed. I miss the step before this, copying the Cal_template and rename it (see step 2 in the old procedure)

My challenging question is, how can I patch sub Distribution with sub ButtonGenerate(SheetName)? Once this is all is connected with each other, the whole reporting works like last week

I know this is a long story, but I am trying to be clear as possible.

In the attachment you can find the final version till now.

If my code is working, you should ask someone who developed other codes.

Jindon,

Your code works perfectly. But the distribution part works now differently and thus I don't know which variable I should pass to the next step (sub ButtonGenerate).
And the distribution part you made yesterday, you skipped the part where it should use the Cal_template.

(I am trying to reach that person that developed the sub ButtonGenerate, but he is not responding )

Unbelievable, I think I have it working
Yes this part, I figure it out... let me test it and I will come back here posting the working tool (hopefully)

Hey guys,

I am still testing the code with different flavors, I did not forget to check this thread "Solved"...

So far, the code works beautifully!!