Consulting

Page 6 of 6 FirstFirst ... 4 5 6
Results 101 to 104 of 104

Thread: Creating Multiple Tables Using Loop in VBA. I still want to add new sheets and add

  1. #101
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    • 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.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  2. #102
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    I have marked this thread as solved since the other tables that I need to add are the same as the pivot ones
    Thank you, I know I have been a pain.

  3. #103
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    By the way is there a way to remove irrelevant sub categories in the filter based on the selection in main LOB filter?

  4. #104
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by jazz2409 View Post
    By the way is there a way to remove irrelevant sub categories in the filter based on the selection in main LOB filter?

    Yes, sort of

    See your other post
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •