- The little table at cell P1 of the Database sheet does not correspond well with the data in the Consolidated sheet - There's no Category 3 LOB in the Consolidated sheet. So the macro will create a Category 3 sheet but the tables on the left will contain no information.
- For the tables on the right, you are putting ALL the LOBs and ALL the Tenure durations on ALL the LOB sheets - you only need to put the relevant LOB's data on each LOB sheet.
- You've put several On Error Resume Next lines in the code which only serves to hide the errors while you're trying to debug. Only have that sort of line when you know that the following code might error and why, and immediately after the suspect lines of code have been executed you should turn that off again with an OnError Goto 0 line.
- The errors that were hidden included you trying to put a pivot table on top of a existing pivot table, exactly the same pivot table. This would have told you that you're building the pivot table too often - you only need to do it once for each time you click the button. So you need to move that code elsewhere.
- For each new sheet, you only need to cycle through the Tenure field. You only need to change LOB field once per new sheet.
See attached.
Reply With Quote
