Consulting

Page 3 of 6 FirstFirst 1 2 3 4 5 ... LastLast
Results 41 to 60 of 104

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

  1. #41
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Quote Originally Posted by p45cal View Post
    In the sheet I attached in the pivot choose category 1b:
    Attachment 25788
    bedtime here.
    If OB AHT = "" or 0, the value of Full AHT is IB AHT.
    If IB Tasks = "" or 0, the value of Full AHT is blank

  2. #42
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Quote Originally Posted by Paul_Hossler View Post
    Pardon me for jumping in so late, but

    1. I think that a PT is the way to go. As a general rule, I like to separate the Data from the Computations from the Presentation

    2. I'd delete the redundant fields such as Date-Time since you have atomic Date and atomic Time

    3. In P45cal's PT, the some calculated fields divide by IB Tasks which sometimes sum to 0

    Attachment 25789

    Yes I thought so, too. I'm just not sure how to go about transferring the data from the Pivot Table to a table most especially if there are missing hours in between. Like for example I ran 9AM and was not able to run 2 consecutive hours then I ran 12PM, PT will look like this:

    Hourly Table IB Tasks ........
    9AM
    12PM

    But then the table will look like this:

    Hourly Table IB Tasks ........
    9AM
    10AM
    11AM
    12PM

  3. #43
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Do I create a Pivot first then the loop to create sheets and tables?

  4. #44
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Give me some time… I'm not full-time on this.
    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.

  5. #45
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by jazz2409 View Post
    Do I create a Pivot first then the loop to create sheets and tables?
    What I've done in the past was to use VBA to create a temporary PT on a temporary WS to do all the heavy lifting and then by selecting the appropriate page fields to 'filter' the data I could create the final reports

    Along the way, I created any needed 'derived' fields for parameters that were not in the source data

    Delete the temporary WS and I was done

    That's just one way

    Sometimes I created a temporary WS with a copy of the source data and any auxiliary values to make the PT easier to use
    ---------------------------------------------------------------------------------------------------------------------

    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

  6. #46
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by jazz2409 View Post
    If OB AHT = "" or 0, the value of Full AHT is IB AHT.
    If IB Tasks = "" or 0, the value of Full AHT is blank
    While trying to implement the above (Full AHT2 in the pic) I got this as opposed to the original Full AHT in the pic.

    Hopefully Full AHT2 is correct; can you confirm?
    2020-01-13_190103.jpg

    edit post posting:
    Now I'm getting the feeling you meant to say:
    If OB AHT = "" or 0, the value of Full AHT is IB AHT/IB Tasks
    ?
    Last edited by p45cal; 01-13-2020 at 12:18 PM.
    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.

  7. #47
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    1. While creating the 'Overall' table at the top of each Lob sheet I note that the Start time (SOO column on the Database sheet) is the same for each LOB - will that ALWAYS be the case? (I'm trying to avoid looking for the latest time in a given LOB.)

    2. The remaining tables are those listed in the Sub LOB for each LOB on the Database sheet; will there ever be additional Sub LOBs in the Consolidated sheet that aren't listed in the Database sheet?

    3. While thinking about that, will there ever be LOBs on the Consolidated sheet that aren't in the Database sheet?

    (Hopefully, you'll tell me that the Database sheet is created from the data on the Consolidated sheet and that ALL the data on the Consolidated sheet is used in creating the data in the Database sheet)
    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. #48
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Quote Originally Posted by p45cal View Post
    Give me some time… I'm not full-time on this.
    First of all I would like to apologize, I didn't mean to make you feel like I'm hurrying you.. I was just trying to analyze as well

    Quote Originally Posted by p45cal View Post
    While trying to implement the above (Full AHT2 in the pic) I got this as opposed to the original Full AHT in the pic.

    Hopefully Full AHT2 is correct; can you confirm?
    2020-01-13_190103.jpg

    edit post posting:
    Now I'm getting the feeling you meant to say:
    If OB AHT = "" or 0, the value of Full AHT is IB AHT/IB Tasks
    ?
    That formula is correct.

    Quote Originally Posted by p45cal View Post
    1. While creating the 'Overall' table at the top of each Lob sheet I note that the Start time (SOO column on the Database sheet) is the same for each LOB - will that ALWAYS be the case? (I'm trying to avoid looking for the latest time in a given LOB.)

    2. The remaining tables are those listed in the Sub LOB for each LOB on the Database sheet; will there ever be additional Sub LOBs in the Consolidated sheet that aren't listed in the Database sheet?

    3. While thinking about that, will there ever be LOBs on the Consolidated sheet that aren't in the Database sheet?

    (Hopefully, you'll tell me that the Database sheet is created from the data on the Consolidated sheet and that ALL the data on the Consolidated sheet is used in creating the data in the Database sheet)
    1. No, the overall table's SOO is always the SOO of the first sub LOB indicated per main LOB. Or maybe we can just add another Sub LOB saying Overall so it will be easier as we will still use the code that you wrote above. Anyway I will also put a column for headcount for each LOB so I think I'll just put another Sub LOB saying Overall
    2. Yes
    3. No

  9. #49
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Quote Originally Posted by Paul_Hossler View Post
    What I've done in the past was to use VBA to create a temporary PT on a temporary WS to do all the heavy lifting and then by selecting the appropriate page fields to 'filter' the data I could create the final reports

    Along the way, I created any needed 'derived' fields for parameters that were not in the source data

    Delete the temporary WS and I was done

    That's just one way

    Sometimes I created a temporary WS with a copy of the source data and any auxiliary values to make the PT easier to use
    Do you have a sample workbook of this that I can study?

  10. #50
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by jazz2409 View Post
    That formula is correct.
    I'm not sure which formula…
    Full AHT2 (15669 in cell M5 of the picture) is correct?
    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.

  11. #51
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    I'm aiming to produce two solutions, one with pivot tables and one with just formulae. This will allow comparison and if they're the same all well and good, you'll have a choice. If they're different then some detective work will be needed.
    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. #52
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Quote Originally Posted by p45cal View Post
    If OB AHT = "" or 0, the value of Full AHT is IB AHT/IB Tasks
    I meant this formula

  13. #53
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    If OB AHT = "" or 0, the value of Full AHT is IB AHT/IB Tasks
    Otherwise, the formula is IB AHT + (OB AHT *(OB TASKS/IB TASKS))

  14. #54
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    I noticed a difference between some pivot table values and plain formula values and drilled it down to the Consolidated sheet cells C377:C458. These cells are different in that they contain date information as well as time information, while the other cells contain only time information.
    In the working model which will it be?
    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. #55
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Quote Originally Posted by p45cal View Post
    I'm not sure which formula…
    Full AHT2 (15669 in cell M5 of the picture) is correct?
    Full AHT is the one that's correct.

    Quote Originally Posted by p45cal View Post
    I noticed a difference between some pivot table values and plain formula values and drilled it down to the Consolidated sheet cells C377:C458. These cells are different in that they contain date information as well as time information, while the other cells contain only time information.
    In the working model which will it be?
    My column A is Date and Time concatenated. I can remove that I think there's no actual use for that.
    My column B has to be just Date.
    And my column C has to be just Time.

    Maybe I accidentally changed something.

  16. #56
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.

  17. #57
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Quote Originally Posted by p45cal View Post
    Attached
    You are amazing works like a charm
    How does this site work, to acknowledge your help?
    Also, is there a website that you can recommend me to learn VBA? I want to be like you

    I need to add a few more tables that have different headers but I will study your code first then I will post back if I can't get something to work

  18. #58
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    How do I add a table at the end of the very last table? This table has different headers than the ones for Sub LOB. I tried to do the same as the Overall Table but it's either:
    1) being added after every table per sheet
    2) being added just after the last table of the very last sheet

    Also, is there a way to merge two cells of a table?

  19. #59
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location

    Post

    Quote Originally Posted by jazz2409 View Post
    How do I add a table at the end of the very last table? This table has different headers than the ones for Sub LOB. I tried to do the same as the Overall Table but it's either:
    1) being added after every table per sheet
    2) being added just after the last table of the very last sheet

    Also, is there a way to merge two cells of a table?
    Sorry I already got this working except the merging of cells

  20. #60
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by jazz2409 View Post
    How do I add a table at the end of the very last table? This table has different headers than the ones for Sub LOB. I tried to do the same as the Overall Table but it's either:
    1) being added after every table per sheet
    2) being added just after the last table of the very last sheet

    Also, is there a way to merge two cells of a table?
      End With
      If cll.Offset(1).Value <> CurrentCat Then
        NewSht.Cells(Rows.Count, "B").End(xlUp).Offset(2).Select
        MsgBox "Code for new table at selected cell here"
      End If
    Next Cll
    New code in blue.

    Merging: Yes; which cells, and why?
    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.

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
  •