Consulting

Page 5 of 6 FirstFirst ... 3 4 5 6 LastLast
Results 81 to 100 of 104

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

  1. #81
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by jazz2409 View Post
    by the way I have another question. I am working on two sites: let's say Site A and Site B. Site A does not require the overall table, but Site B does. How do I do that?
    There is no site data in the files you've attached here.
    Is the site information to be found in column R of the Database sheet?
    Will the site be the same for every row on the Consolidated sheet (Column U?)?
    Can it change from LOB to LOB?
    Can it change from Sub LOB to Sub LOB within a LOB?
    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. #82
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Quote Originally Posted by p45cal View Post
    There is no site data in the files you've attached here.
    Is the site information to be found in column R of the Database sheet?
    Will the site be the same for every row on the Consolidated sheet (Column U?)?
    Can it change from LOB to LOB?
    Can it change from Sub LOB to Sub LOB within a LOB?
    Is the site information to be found in column R of the Database sheet? - Yes
    Will the site be the same for every row on the Consolidated sheet (Column U?)? - No
    Can it change from LOB to LOB? - Yes
    Can it change from Sub LOB to Sub LOB within a LOB? - Yes


    I attached your workbook with Site information..

    Also, is there a way to remove IB, OB, and FAHT from the table headers below and leave < 90 days or > 90 days while still being able to apply their corresponding formula?

    IB > 90 days IB < 90 days OB > 90 days OB < 90 days FAHT > 90 days FAHT < 90 days
    Attached Files Attached Files

  3. #83
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by jazz2409 View Post
    Site A does not require the overall table, but Site B does. How do I do that?
    The overall table is at the bottom (edit: that should be top) of each LOB sheet which contains several sub LOBS. Some of those sub LOBS are on Site A, others on Site B [Can it change from Sub LOB to Sub LOB within a LOB? - Yes]. What to do?
    (I haven't looked at your latest attachment yet.)

    Quote Originally Posted by jazz2409 View Post
    Also, is there a way to remove IB, OB, and FAHT from the table headers below and leave < 90 days or > 90 days while still being able to apply their corresponding formula?
    Yes, that will be included in the next version.
    Last edited by p45cal; 01-22-2020 at 10:54 AM.
    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.

  4. #84
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    It will only be based on the Main LOB.. So if the main LOB is based on Site 1, it shouldn't show the overall table.. But if the Main LOB is based in Site 2, it should shoe the overall table.

  5. #85
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by jazz2409 View Post
    It will only be based on the Main LOB.. So if the main LOB is based on Site 1, it shouldn't show the overall table.. But if the Main LOB is based in Site 2, it should shoe the overall table.
    This is crazy!
    In the new workbook with Site data the main LOB Category 1 has both sites involved!
    Should I work based on if the LOB has any row in the Consolidated sheet with Site 2 in column U it should show the overall table at the top?
    It's the same as saying that for a given LOB on the Consolidated sheet, only if all its rows have Site 1 in column U do we not show the Overall table.
    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.

  6. #86
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Hmm I'm also not sure how to do it.. There are circumstances where a Main LOB has one of its Sub LOBs based on a different site than the other Sub LOBs of that Main LOB..

  7. #87
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by jazz2409 View Post
    Hmm I'm also not sure how to do it.. There are circumstances where a Main LOB has one of its Sub LOBs based on a different site than the other Sub LOBs of that Main LOB..
    In the absence of guidance on this, the attached contains code to deal with:
    Quote Originally Posted by jazz2409 View Post
    Also, is there a way to remove IB, OB, and FAHT from the table headers below and leave < 90 days or > 90 days while still being able to apply their corresponding formula?
    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.

  8. #88
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Hmm sorry I'm not sure if something was changed.. I clicked on Add Tables button but Category 2 still showed the Overall table..

  9. #89
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Whats' changed are the headers of the bottomost table for each LOB.
    I'm not going to code for missing out the Overall table until I know the conditions of it appearing or not - you yourself don't seem to know, so I can hardly guess.
    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.

  10. #90
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Hello, sorry I got word to not use the Sub LOB's site and use the Main LOB's site instead.
    Attached Files Attached Files

  11. #91
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    The attached decides whether to include the Overall table by looking at the FIRST row for each Main LOB in the Database sheet range P1:Rn. In your sample data the actual cells looked at are cell R2 for Category 1 and cell R6 for Category 2. No other cells in column R are examined. So you just have to make sure the first ones are correct in that table.
    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.

  12. #92
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Works very well. Thank you so much you've been very helpful. :angel:
    I still need to add a few more tables that will be converted from pivot tables. I will continue studying your code and I will try to add the tables myself first then I will post back if I can't get something to work

  13. #93
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    I have a question. Sorry I can't think of how to do this.


    How do I loop through the filters to create a specific pivot table then convert that into a table?


    I edited the code on Module 3 to make it look exactly as how I need it.


    For the first two pivot tables I need them to show > 90 days tenure data and < 90 days tenure data then convert them into a table (example is in Sheet Category 1).
    Attached Files Attached Files

  14. #94
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Snippet for you:
    Sub blah()
    Set PT = Sheets("Sheet6").Range("B6").PivotTable
    Set Destn = Sheets("Sheet6").Cells(Rows.Count, "W").End(xlUp).Offset(3)
    With PT
      Set pf = .PageFields("Tenure")
      For Each pit In pf.PivotItems
        pf.CurrentPage = pit.Name
        With .TableRange1
          .Copy
          Destn.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
          Application.CutCopyMode = False
          Set myTable = Sheets("Sheet6").ListObjects.Add(xlSrcRange, Destn.Resize(.Rows.Count, .Columns.Count), , xlYes)
          myTable.TableStyle = "TableStyleMedium14"
          myTable.ShowTableStyleRowStripes = False
          Set Destn = Destn.Offset(.Rows.Count + 2)
        End With    '.TableRange1
        myTable.Unlist
      Next pit
    End With    'PT
    End Sub
    Last edited by p45cal; 01-27-2020 at 08:23 AM.
    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.

  15. #95
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Hi, sorry may I ask you to kindly explain the code to me?I kind of don't understand it.. Also how do I add a title to each table? Like this table is for < 90 days, the other is for > 90 days?

  16. #96
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    It takes me a lot longer to explain the code than to write it.
    We can do it piecemeal; tell me one or two things you don't understand and I'll try.
    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.

  17. #97
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Adding titles to each table.
    You've seen the code which does this for the previous tables, you just need to tweak it and add it.
    Sub blah()
    Set PT = Sheets("Sheet6").Range("B6").PivotTable
    Set Destn = Sheets("Sheet6").Cells(Rows.Count, "W").End(xlUp).Offset(3)
    With PT
      Set pf = .PageFields("Tenure")
      For Each pit In pf.PivotItems
        With Destn
          .Value = pit.Name
          With .Font
            .Name = "Calibri"
            .Size = 11
            .Underline = xlUnderlineStyleSingle
            .Bold = True
          End With
        End With
        Set Destn = Destn.Offset(2)
        pf.ClearAllFilters 'added this line (makes it a bit more robust).
        pf.CurrentPage = pit.Name
        With .TableRange1
          .Copy
          Destn.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
          Application.CutCopyMode = False
          Set myTable = Sheets("Sheet6").ListObjects.Add(xlSrcRange, Destn.Resize(.Rows.Count, .Columns.Count), , xlYes)
          myTable.TableStyle = "TableStyleMedium14"
          myTable.ShowTableStyleRowStripes = False
          Set Destn = Destn.Offset(.Rows.Count + 2)
        End With    '.TableRange1
        myTable.Unlist
      Next pit
    End With    'PT
    End Sub
    At msg#97 in this thread you've received plenty of help. You need to do some working out for yourself.
    You're getting paid for using my code, I'm not. I'm not going to spoonfeed you.
    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.

  18. #98
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Hmm just please correct me if my understanding is wrong.

    The Set pf = .PageFields("Tenure") part selects the specific pagefield to be filtered and then

    For Each pit In pf.PivotItems 'loops through everything that's in the array
    pf.CurrentPage = pit.Name 'selects specific item inside the array
    With .TableRange1 'creates table
    .Copy


    That's what I understood

  19. #99
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by jazz2409 View Post
    Hmm just please correct me if my understanding is wrong.

    The Set pf = .PageFields("Tenure") part selects the specific pagefield to be filtered
    pf becomes an object which is part of the pivot table

    Quote Originally Posted by jazz2409 View Post
    For Each pit In pf.PivotItems 'loops through everything that's in the array
    loops through the items in the dropdown of that field.
    Quote Originally Posted by jazz2409 View Post
    pf.CurrentPage = pit.Name 'selects specific item inside the array
    filters that page field to one of the items.
    Quote Originally Posted by jazz2409 View Post
    With .TableRange1 'creates table
    .Copy
    .TableRange1 is a range of cells on the worksheet, being the main body of the pivot table less the pagefields at the top.
    .Copy copies that range of cells to the clipboard, later pasting that data into other cells. The table is not created as a table (a proper Excel Table) until the line beginning Set myTable = is executed.
    Using the With .TableRange1… …End With construct means that everything within it which starts with a dot refers to .TableRange1
    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.

  20. #100
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    I was able to make those tables appear on each sheet of each LOB. However it's not filtering based on each LOB. It only filters one specific LOB then it is being copied to each sheet.


    What am I doing wrong?
    Attached Files Attached Files

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
  •