There is no site data in the files you've attached here.Quote:
Originally Posted by jazz2409
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? - YesQuote:
Originally Posted by p45cal
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
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?Quote:
Originally Posted by jazz2409
(I haven't looked at your latest attachment yet.)
Yes, that will be included in the next version.Quote:
Originally Posted by jazz2409
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!Quote:
Originally Posted by jazz2409
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.
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
Quote:
Originally Posted by jazz2409
Hmm sorry I'm not sure if something was changed.. I clicked on Add Tables button but Category 2 still showed the Overall table..
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.
Hello, sorry I got word to not use the Sub LOB's site and use the Main LOB's site instead.
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.
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 :)
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).
Snippet for you:Code: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
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?
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.
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.At msg#97 in this thread you've received plenty of help. You need to do some working out for yourself.Code: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
You're getting paid for using my code, I'm not. I'm not going to spoonfeed you.
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
pf becomes an object which is part of the pivot tableQuote:
Originally Posted by jazz2409
loops through the items in the dropdown of that field.Quote:
Originally Posted by jazz2409
filters that page field to one of the items.Quote:
Originally Posted by jazz2409
.TableRange1 is a range of cells on the worksheet, being the main body of the pivot table less the pagefields at the top.Quote:
Originally Posted by jazz2409
.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
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?
- 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.
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.
By the way is there a way to remove irrelevant sub categories in the filter based on the selection in main LOB filter?
Quote:
Originally Posted by jazz2409
Yes, sort of
See your other post