Update existing VBA script to Breakdown Data and Transferring to another sheet

So many subs which one you want ?
Gowflow

In the attached workbook I verified that the searches, Delete Service Item and Quotes by Month all work. For the breakdowns I set up scenarios 1 to 5. I verified that 1 to 4 work but I'm uncertain about 5. Please test 5 and let me know if it's OK. I did not attempt to try to replicate the old discount scenarios since their structure does not conform to the new way of handling discounts, so please add scenario 6 to this workbook and test the breakdown. It probably won't work so please show me what it should look like.
29179716.xlsm

Here is what Scenario 5 should look like.  I will work on scenario 6.

Melbut
I am not familiar with your workbook but I could help if you want just answer my question please what sub are we talking about ?
Gowflow

GowFlow, the code that meeds modifying is the code associated with the 'Breakdown Quote' button on frmMaint.

Melbut, the data that I used to recreate the scenarios lacked renewal dates so I manually added them in the Original Quotes sheet. Currently the SI validation process requires a renewal date if the order term is more than 12, so two questions:

1. Should there be additional validation?
2. What should the renewal date be for scenario 5?

It also can't be after the end date.

@Martin I guess your well involved in this project and fully trust your ability to assit OP I will withdraw.
Gowflow

Yes, I've worked on several versions of this for at least a year.

I just noticed that in your breakdown of scenario 5 you show several rows where 'J' is 12/30/2019 rather than 12/31/2019.  I assume the latter is correct.

validation process requires a renewal date if the order term is more than 12, so two questions:

1. Should there be additional validation? Not sure about that

The after end date validation may need to be removed for now.  I have to get more information on the renewal date and how it should be handled since we are working with two different accounting cycles (the vendor and our own). So at the moment I am not sure how this needs to be handled.
Question: If I enter the same renewal date and year for each SI that gets added to the original quote sheet how can this break down according to year on the RAW-DATA sheet?

1. What should the renewal date be for scenario 5

The issue we are running into with the renewal date is that we are trying to fit our vendor's payment schedule into our annual cycle.

using the dates in scenario 5:
Although, this is a contract for 35 months. we have to request a renewal every year in order to release funds and this is based off of our vendors payment schedule and not ours.  
The vendor payment schedule looks like this and therefore the renewal date starts in May for  2019 to 2021 and then April for 2022.
 
Also, I am not exactly sure how they are handling renewals when two different order terms show up under the same quote. Therefore, I think all validations should be removed for now except for a message indicating that if the term is greater than 12 a renewal date is required, but it allows me the choice for now to either enter or not enter.  This would be temporary until I get more information how the renewal should function.  

I think all validations should be removed for now except for a message indicating that if the term is greater than 12...

Is there any way, now or in the future, that a renewal date could be after the end date?

...but it allows me the choice for now to either enter or not enter.

I'm going to stop working on this until you reach a final decision because currently there's code in the breakdown process that depends on the presence  or absence of a renewal date.

Is there any way, now or in the future, that a renewal date could be after the end date? 

I do not foresee a renewal happening after the end date, but like i said it would be easier to just allow the manual entry without the validation on this field with the exception of those SIs greater than 12 months.  

I'm going to stop working on this until you reach a final decision because currently there's code in the breakdown process that depends on the presence  or absence of a renewal date. 

Not sure why we can't continue without the renewal date piece.  I can continue to add this piece in manually to each SI if need be.  As I may not get the feedback for a week or so.

Let me see if I can get an expedited answer

Not sure why we can't continue without the renewal date piece.

Because in the breakdown there is code that says "If there's no renewal date do one thing, otherwise do a different thing".

Here's what I found out regarding the renewal dates. The renewal dates which are based off of the Vendor payment schedule.
So using scenario 5 on the RAW_DATA sheet; the renewal date would look something like this:Renewal should not be greater than end date of 4/7/2022

Please double-check all the dates in the scenario 5 OQ data because I believe at least the 4/7/2022 end date for the first SI is incorrect. I'm going to assume that it should be 4/7/2021.

In addition to the above, in rows 62 to 64 you show 'T' as 7.772311828 which I believe is correct, but in rows 65 and 68 you show something else even though the start and end dates are the same as rows 62 to 64. I'm going to assume that both of them should be 7.772311828.

I also need to know what scenario 3 and 4 look like now that we have a Discount% column.

In the attached is a snippet of a quote from our vendor that the data for Scenario 5 is derived from.   From the quote snippet there is an annual pricing summary schedule for quotes with terms greater than 12.  This pricing schedule will not show on the quote if the terms are 12 months or less. And only renewal dates are needs for terms greater than 12.  
I am sure I have mentioned this before but it may not have been clear.  The Vendor annual renewal is a complete year from the start date.  For example,  if the start date is 5/8/18 then 12 months from that date is 5/7/19 and thus renewal is expected on 5/8/19.  
We are tracking start and end dates, but from the breakdown piece its not based on a full 12 months from the start date. It's based pm an exact calendar year.  For example,  if the start date is 5/8/18 then the end date is 12/31/18 which has nothing to do with when the vendors expected end date to renewed.  
So, I don't think we need to rethink this a bit, but most certainly don't want this to be a cause to hold up the progress.  Therefore, is there any possible workarounds to this.

1. As a suggestion and hopefully it will not cause a lot of rework. But maybe there is a way to  add to the user form the vendors Annual pricing summary schedule for any quote that is greater than 12 months and once the 45 days prior to  renewal from the pricing schedule is reached drives the notification message for that quote.

scenario 5.xlsx

In addition to the above, in rows 62 to 64 you show 'T' as 7.772311828 which I believe is correct, but in rows 65 and 68 you show something else even though the start and end dates are the same as rows 62 to 64. I'm going to assume that both of them should be 7.772311828. 

I had to redo this quote as I realized that when I initially set this up I forgot that my amount never matched the vendor amount and because at the time it was added to the spreadsheet I didn't have the userform and was still trying to workout the formula to Match up the amounts. So, it's possible Col T still may not be totally exact.  We may need to allow for a small discrepancy and just add the difference to either one of the SIs in the first year or in the last year and build into the comments the discrepancy dollar value and the reason (i.e., due to the monthly/Unit price on the quote being rounded to two decimal places and not showing the full actual price caused a small discrepancy in the amount which as been forcefully added to SI on row ???"

This is only suggestions..

The breakdown total in your scenario 5.xlsx does not match the OQ total.

In the attached workbook I've changed the breakdown code so that doesn't care if there is a renewal date or not. The presence of renewal date had been used to trigger multi-year processing but I realized that that was stupid since I can tell if an SI is multiyear by simply comparing the start and end dates.

My results for scenario 5 are close to but not the same as your breakdown in scenario 5.xlsx, but the total is exactly the same as the OQ total. In previous workbooks SI totals almost always came out the same as the OQ row total but that was because I kept track of how much of column 'T' was used and at the end of processing for any given SI, the last breakdown total for that SI was based on total 'T' minus used 'T" and sometimes that resulted in negative 'T'. To see what I'm talking about see RAW_DATA rows 41 and 44 in workbook 29175348d.xlsm if you still have it around or if you download it again.

In the attached workbook I don't do anything like that and each breakdown row is based on its actual 'T'. For scenario 5, as I said, the total exactly matches the OQ total, but if further testing shows that that's not always the case I need a method for adjusting the last breakdown SI. I'm thinking of something like if the RD total is .001% (for scenario 5 that would be $5.00) higher or lower than the OQ total then adjust the last SI to make the totals equal. Please let me know if that’s a good idea or not.

I've tested scenarios 1 and 2 and they are exactly correct, but need to know what scenario 3 and 4 look like (if they are still valid tests) now that we have a Discount% column. I also eventually need scenario 6.
29179716a.xlsm

Martin,
I think your suggestion will work.  I had to explained to my management that if the vendor can not provide the actual pricing with out  rounding its to much work for us to get a complete exact match on some SIs.  But we wil try to work into the userform and  spreadsheet a way to identify, document and add in the difference.

I will work on 3 and 4 and provide that hopefully sometime tomorrow.

If there's a difference I could add a note in the comment field of the last SI documenting the adjustment. I'll wait until it turns out that any adjustment is needed,

Here is Q3.  I did not work Q4 since Q3 had some issues and thought I would have the same types of problems with Q4.  
1. The add to the original quote sheet looks good.
2.  The breakdown of the start and end dates looks good.  
3.  there was issue on breakdown of the discount and total price in general.
4.  As you can see in the screen shot the RAW_DATA total is different from the Qriginal Quote Total.
29179716a.xlsm

I did not expect it to work but I'm sure I can correct it.

For 3 and 4, why are you entering the discount percent in the userform's Monthly Price field rather than the new Discount % field?

For 3 and 4, why are you entering the discount percent in the userform's Monthly Price field rather than the new Discount % field? 

I thought the the discount was going to be driven by the Discount Frame fields.  So I selected the Qty and entered the discount in that frame.

Infact I thought all the discount % will show in the discount column on the spreadsheet

Ah, OK then it's my problem.

I don't understand how you got that result. I...

1. Deleted the last SI for Q-33333333,
2. Selected Add New
3. Chose a License Type
4. Entered 500000 in the Quantity field
5. Selected Qty
6. Entered .15 in Discount % (Note the decimal point)
7. Clicked Add Service Item

When I did that I noticed a couple of problems where the Total Price field in the userform remained blank until I selected a different SI and then reselected the one I had just added. When I did that an incorrect total price showed up, but the important thing is that the .15 percent correctly showed up in the Discount% field of both the userform and the OQ sheet! Please double check what you did.

A few questions. Is the quantity of 500000 meaningful? What I mean by that is there some contractual thing where you give a discount to 500000 things, or is it just an artifact that you are using to get a discount of $750? Would you like to be able to add a SI where you would not enter a quantity and just select the Total Price option and then enter 750 into  the Total Price field? (you can't do that now)

I will go back and reproduce Q-333333 following your steps.

A few questions. Is the quantity of 500000 meaningful? What I mean by that is there some contractual thing where you give a discount to 500000 things, or is it just an artifact that you are using to get a discount of $750? 

Yes, the vendor has several ways to discount prices either by setting discounts across an entire quote or on individual products and product groups.

Would you like to be able to add a SI where you would not enter a quantity and just select the Total Price option and then enter 750 into  the Total Price field? (you can't do that now) The quantity is a true representation of quote qty. 

Not sure If I am following this.

This discount is based on the Qty and not the total price.  The discount and qty determines the total price. for that specific SI and does not create a new SI Row.

the discount on a Total price SI creates a completely new SI row from the discount applied to 1 or more total price items under the same quote.

I interpret what you are say as "yes we do sell 500000 of a certain product and in this case we want to charge him $750". If that's correct could the same thing be accomplished by selling with a quantity of 1 and a price of $750?

I interpret what you are say as "yes we do sell 500000 of a certain product and in this case we want to charge him $750". If that's correct could the same thing be accomplished by selling with a quantity of 1 and a price of $750?

No, because we lose sight of the purchase qty count

Did you get the same results that I did when you followed my steps?

It's always bothered me that we add quantity discount percents to the quote total, but I accept the fact that that works for you.

Looking at the breakdown you did for Q-3333333, why is every Order Term = 0.5?

Did you get the same results that I did when you followed my steps?

Working on that now.

It's always bothered me that we add quantity discount percents to the quote total, but I accept the fact that that works for you. 

As long as all the percents are captured in the discount % column vice under the monthly price.  Can you show me via a screen shot how you envision this.  I am always amenable to a better way.  I just need to keep a visible record history of SIs with discount percents
 

Looking at the breakdown you did for Q-3333333, why is every Order Term = 0.5? 

This is done on SIs with discounts only, but It doesn't have to be this way.  Honestly I didn't know of any other way to break this down via a formula when there are no term month.  If you have a better way to distribute for example Q-3333333 discount of 750. Please let me know. 

1. Deleted the last SI for Q-33333333, 

Here is what happen when I tried to delete the last SI.  Because this feature is not working for me I ended up manually deleting the quote. See screen shots and comments on the steps I use to delete this quote using the userform.

1. Selected Add New
2. Chose a License Type
3. Entered 500000 in the Quantity field
4. Selected Qty
5. Entered .15 in Discount % (Note the decimal point)
6. Clicked Add Service Item

I followed the above steps exactly and here are the results for Q-33333333:
29179716a.xlsm
 Original Quote Sheet Results
 RAW_DATA Sheet Results

I'm totally confused about discounts and some other things so please answer these questions.

1. I asked about your original breakdown of Q-33333333 pointing out that EVERY SI had an order term of .05 and asking you why that was. In your answer you seemed to be saying that EVERY SI in the quote should have .5, just because there's one quantity discount. Is that true? If so we've never done it that way before.
2. When you want to delete an SI, why do you use Search? Search was designed to show you data for the matched SIs including totals and nothing more. To delete an SI all you need to do is select it in the normal (non-search) view of the SIs and then click the Delete Service Item button.
3. You said "I followed the above steps exactly and here are the results for Q-33333333" but you didn't say if the OQ data was correctly generated. Was it?
4. You also showed me what I believe are the results of breaking down that data, and I assume they are NOT correct, but have you shown me what the results should be?

Also, it seems that you have started copying and pasting pictures into your posts rather than using the 'Insert Image' button. I would prefer you do it that way because the details in you copy/pasted pictures are in some cases too small for my old eyes to read and I can't easily expand them.

I asked about your original breakdown of Q-33333333 pointing out that EVERY SI had an order term of .05 and asking you why that was. In your answer you seemed to be saying that EVERY SI in the quote should have .5, just because there's one quantity discount. Is that true? If so we've never done it that way before. 

No that is not true .  You have to keep in mind that whatever the VBA script can not do at present I still have to manually figure how to manipulate the spreadsheet and possible give you something to work off of albeit not always the best method or way of doing it.  So in this particular case I was trying take the discounted SI with no term month and manually come up with a way to breakdown the discount  on the RAW_DATA sheet  because

1. The total price calculation is driven off of col T and at the time I do not believe the breakdown on discounts SI with no term month was built into the script to handle the calculation and,
2.  therefore, I had to figure out a way to still do the breakdown on discounted SIs and so I arbitrarily decided to use .5 in the case for Scenario Q-3333333 as a fictitious term month so that the discount total price could be spread out over the breakdown years,
3. And in the screen shot the discount of total price of 750 was divided by .5 because the start and end fell over two years 2019 and 2020.

The bottom line the term of .5 is a random term to drive the discount breakdown and nothing more.  If the script can handle this then great. 

When you want to delete an SI, why do you use Search? Search was designed to show you data for the matched SIs including totals and nothing more. To delete an SI all you need to do is select it in the normal (non-search) view of the SIs and then click the Delete Service Item button.

Ok.  Not a problem.  I may not fully grasp how to perform some of the function correctly which is causing a bit of confusion, therefore can you provide.
1.  What is the normal (non-search) view? Can you provide instruction on that.

I will eventually will need to do a how to write up on all the buttons/functions.

You said "I followed the above steps exactly and here are the results for Q-33333333" but you didn't say if the OQ data was correctly generated. Was it? 

Yes the OQ data did generate correctly.  See screen shot.  Top portion is the OQ result.

You also showed me what I believe are the results of breaking down that data, and I assume they are NOT correct, but have you shown me what the results should be? 

The correct result for the breakdown is in the second half of the screen shot. Keep in mind that the .5 used for term month is a fictitious random number to drive the proportion of breakdown for the discount total price.  Again, this is the only way I know how to manually drive the calculation if your VBA script handles this not a problem and all the better. 

I'm sorry I didn't notice your message about the image size as I was tacking one question at a time.  I will keep this in mind.  However, most of the screenshots are embedded on the worksheet that I attach.

In the attached workbook scenarios 1 to 6 all work. Also, while when you want to delete an SI I still think that it's easier to scroll down the list to find and select the one to be deleted, you can now safely do it via Search. I've also expanded the "Deletion Verification" message to show some fields from the selected item to aid in its identification. I know that you've told me what fields make an SI unique but I don't remember so if you what me to modify the new message it's easy to do.
29179716b.xlsm

Martin,

1. I started testing the workbook for all the scenarios.
2. I deleted all the data on both OQ and RD sheets
3. I checked the Add New Box
4. I entered Q-11111111. However, when I got to the 8 digit after the "Q-" I got a run-time error.

See attachment: Add New Error.xlsx. Two screens are embedded.

What that is saying is that while there may be no data on the OQ sheet and the RAW_DATA sheet, that there are SIs showing in the userform. Did you perhaps delete the data from the two sheets while the userform was open?

Anyhow make sure there's no data in the two sheets, close and reopen the workbook and try again.

Nevermind, I can reproduce the error.

Corrected.
29179716c.xlsm

Ok, thanks.

Using the latest workbook I completed the testing for scenario 1-6.  Everything worked as expected.

Martin,

I am ready to move forward with a couple of other things, but ran into an issue when testing the Text to table button.  I  am getting an error when working on data in the Text to table view. Table to Text issue.xlsx

1. I am trying to manually add  links to the Quote PDF from a sharepoint stored location to the Original Quote sheet. I was planning to add the link  after the SI key, to which this column with the link was not going to be part of the table because I didn't want to cause the code to mess up.  Although, I should be able to manually manipulate data from the sheets themselves without any issues regardless of being in the table or text view.

2. New question, but I also, would like to have a some sort of toggle button that removes and add the subtotals on the original quote sheet as I can not perform a pivot table with the subtotals.

2. I am also trying to create a relationship between the Original Quotes and RAW_DATA sheet, so that I can create pivot tables but both sheets need to be in a table format.  

However, when I tried to flip back and forth I keep getting  a run-time error and all data gets hidden when I try to go back to the text view..

Text to table works for me except that it does not seem to include the last total line on the OQ sheet when it changes that data into a table.

In the picture that you posted it shows the Quote Maintenance and Table to Text buttons under columns AF and AG. Does it always look that way for you?

While I can't reproduce the problem you reported, the attached workbook should fix it. If you want to try the modified code in your workbook

1. In the attached workbook, go to Visual Basic via Alt-F11
2. In Module1,  locate the ToggleTable sub
3. Copy the complete sub
4. In your workbook, go to Visual Basic
5. In Module1,  locate the ToggleTable sub
6. Select the entire sub
7. Paste
8. Don't save the workbook until you are happy with it

About the position of the buttons - I'm using an Excel feature that is supposed to lock them in place, but if that isn't working for you when you open the attached workbook I can add code that moves them so that they look like this every time you open the workbook.
29179716d.xlsm

In the picture that you posted it shows the Quote Maintenance and Table to Text buttons under columns AF and AG. Does it always look that way for you? 

Actually I never notice this.

About the position of the buttons - I'm using an Excel feature that is supposed to lock them in place, but if that isn't working for you when you open the attached workbook I can add code that moves them so that they look like this every time you open the workbook. 

I do know the buttons are not locking in place. However, I will need to test again, the team name and license type 2 subtotals buttons because when I tested them last they were not subtotaling correctly or I believe I got some type of run-time error.  In addition, we never completed the email notification based on renewal dates so, the Start timer does pop-up upon opening the worksheet, but has no other function as far as know.  

Martin,

What I am noticing is that I'm having a difficult time when copying historical data from an old workbook into latest version of the workbook. Here's what I found happening when doing the copy and paste.

1.  Because I have to run analysis on both sheets I have to remove the subtotals from the original sheet.  Reason why I ask if on the original quote sheet a toggle button can be created similar to the Text to table button, but with subtotals.
2. When copying and pasting I noticed the data columns with formulas always link back to the previous version workbook to which I end up having to manually remove the links each and every time in order to utilize the current workbook version. This is definitely time consuming.  I am not sure if there is an easier way to do this and if so please let me know. 

Thanks Martin.

I will test workbook out.