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
Do I create a Pivot first then the loop to create sheets and tables?
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.
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
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.
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.
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
That formula is correct.
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
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.
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))
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.
Full AHT is the one that's correct.
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.
Attached
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.
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
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?
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.