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

[jazz2409]

So, I need to add new sheets and add tables in these new sheets, using vba. As shown in the image below, there are two column Main Category and Sub Category. I want to create new sheet for every Main Category and add tables for every Sub Category based on the sheet it belongs to. Additionally I may add new entries to Main Category and Sub Category, the vba code should add sheet and tables for those as well. The only difference is that I need to create multiple tables on button click and the times that it will list is from the start time that's indicated on the below table to the current time.

25753

The Main Category becomes the sheet name and the Sub Categories per Main Category becomes each table's Title/Subject and NOT a header.
So for example, the first sub category's start time is 1AM and right now let's say it's 4AM. The tables should look like this:

25754

If the Sub Category's start time is later than the current time, it should only show the Sub Category's name and the headers. So for example, let's use Main Category 2. It should look like this:

25755

This is what I have so far:



Sub CreateSheetsFromAList()
Dim MyCell As Range, myRange As Range
Dim MyCell1 As Range, myRange1 As Range
Dim WSname As String

Sheet1.Select
Range("A2").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Set myRange = Selection
Application.ScreenUpdating = False

For Each MyCell In myRange
If Len(MyCell.Text) > 0 Then
'Check if sheet exists
If Not SheetExists(MyCell.Value) Then

'run new reports code until before Else

Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet

WSname = MyCell.Value 'stores newly created sheetname to a string variable


End If
End If

Next MyCell

End Sub

Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
Dim sht As Worksheet

If wb Is Nothing Then Set wb = ThisWorkbook
On Error Resume Next
Set sht = wb.Sheets(shtName)
On Error GoTo 0
SheetExists = Not sht Is Nothing
End Function

Public Sub ChooseSheet(ByVal SheetName As String)
Sheets(SheetName).Select
End Sub






I also posted it here: https://stackoverflow.com/questions/59613701/creating-multiple-tables-using-loop-in-vba

Please help thank you

[jazz2409]

Here's the sample file

[p45cal]

Try:
Sub blah()
Dim NewSht As Worksheet
Set Rng = Sheets("Database").Cells(1).CurrentRegion
Set Rng = Intersect(Rng, Rng.Offset(1)).Resize(, 1)
CurrentCat = ""
For Each cll In Rng.Cells
If cll.Value <> CurrentCat Then
If Not NewSht Is Nothing Then NewSht.Columns("B:B").EntireColumn.AutoFit
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
CurrentCat = cll.Value
End If
Set Destn = NewSht.Cells(Rows.Count, "B").End(xlUp).Offset(2)
With Destn
.Value = cll.Offset(, 1).Value
With .Font
.Name = "Calibri"
.Size = 11
.Underline = xlUnderlineStyleSingle
.Bold = True
End With
End With
Set Destn = Destn.Offset(2)
Destn.Resize(, 13).Value = Array("Hourly Table", "Column 1", "Column 2", "Column 3", "Column 4", "Column 5", "Column 6", "Column 7", "Column 8", "Column 9", "Column 10", "Column 11", "Column 12")
Set Destn = Destn.Offset(1)
StartTime = cll.Offset(, 3).Value
If TypeName(StartTime) = "String" Then StartTime = TimeValue(StartTime)
EndTime = cll.Offset(, 4).Value
If TypeName(EndTime) = "String" Then EndTime = TimeValue(EndTime)
For hr = StartTime To EndTime Step 1 / 24
Destn.Value = hr
Destn.NumberFormat = "hh:mm AM/PM"
Set Destn = Destn.Offset(1)
Next hr
Next cll
If Not NewSht Is Nothing Then NewSht.Columns("B:B").EntireColumn.AutoFit
End Sub

Notes:
1. 12AM is midnight, 12PM is noon. You need to get these right on the Database sheet.
2. If you tell me how you formatted the green areas I'll add that in.

edit post posting: re-reading your msg#1 of this thread I see I've missed some points - give me some time…

[p45cal]

Some changes:
Sub blah()
Dim NewSht As Worksheet, LastTable As ListObject
Set Rng = Sheets("Database").Cells(1).CurrentRegion
Set Rng = Intersect(Rng, Rng.Offset(1)).Resize(, 1)
'Rng.Select
CurrentCat = ""
EndTime = Application.WorksheetFunction.Floor_Math(Time, 1 / 24)
For Each cll In Rng.Cells
If cll.Value <> CurrentCat Then
If Not NewSht Is Nothing Then NewSht.Columns("B:B").EntireColumn.AutoFit
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
NewSht.Name = cll.Value
CurrentCat = cll.Value
End If
Set Destn = NewSht.Cells(Rows.Count, "B").End(xlUp).Offset(2)
If Not LastTable Is Nothing Then LastTable.Unlist
With Destn
.Value = cll.Offset(, 1).Value
With .Font
.Name = "Calibri"
.Size = 11
.Underline = xlUnderlineStyleSingle
.Bold = True
End With
End With
Set Destn = Destn.Offset(2)
Destn.Resize(, 13).Value = Array("Hourly Table", "Column 1", "Column 2", "Column 3", "Column 4", "Column 5", "Column 6", "Column 7", "Column 8", "Column 9", "Column 10", "Column 11", "Column 12")
Set Destn = Destn.Offset(1)
StartTime = cll.Offset(, 3).Value
If TypeName(StartTime) = "String" Then StartTime = TimeValue(StartTime)
' EndTime = cll.Offset(, 4).Value
' If TypeName(EndTime) = "String" Then EndTime = TimeValue(EndTime)
For hr = StartTime To EndTime Step 1 / 24
Destn.Value = hr
Destn.NumberFormat = "hh:mm AM/PM"
Set Destn = Destn.Offset(1)
Next hr
Set LastTable = NewSht.ListObjects.Add(xlSrcRange, Destn.Offset(-1).CurrentRegion, , xlYes)
With LastTable
.TableStyle = "TableStyleMedium14"
.ShowTableStyleRowStripes = False
'.Unlist
End With

Next cll
If Not LastTable Is Nothing Then LastTable.Unlist
If Not NewSht Is Nothing Then NewSht.Columns("B:B").EntireColumn.AutoFit
End Sub

The end time is the current time rounded down to the nearest hour. If you want it rounded up to the nearest hour then change Floor_Math to Ceiling_Math.
I've had a go at some formatting by temporarily converting to listobjects (Tables) and then converting back to a plain ranges.
If a sheet already exists, what do you want to happen?

I'll stop there and wait to hear from you.

[jazz2409]

Some changes:
Sub blah()
Dim NewSht As Worksheet, LastTable As ListObject
Set Rng = Sheets("Database").Cells(1).CurrentRegion
Set Rng = Intersect(Rng, Rng.Offset(1)).Resize(, 1)
'Rng.Select
CurrentCat = ""
EndTime = Application.WorksheetFunction.Floor_Math(Time, 1 / 24)
For Each cll In Rng.Cells
If cll.Value <> CurrentCat Then
If Not NewSht Is Nothing Then NewSht.Columns("B:B").EntireColumn.AutoFit
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
NewSht.Name = cll.Value
CurrentCat = cll.Value
End If
Set Destn = NewSht.Cells(Rows.Count, "B").End(xlUp).Offset(2)
If Not LastTable Is Nothing Then LastTable.Unlist
With Destn
.Value = cll.Offset(, 1).Value
With .Font
.Name = "Calibri"
.Size = 11
.Underline = xlUnderlineStyleSingle
.Bold = True
End With
End With
Set Destn = Destn.Offset(2)
Destn.Resize(, 13).Value = Array("Hourly Table", "Column 1", "Column 2", "Column 3", "Column 4", "Column 5", "Column 6", "Column 7", "Column 8", "Column 9", "Column 10", "Column 11", "Column 12")
Set Destn = Destn.Offset(1)
StartTime = cll.Offset(, 3).Value
If TypeName(StartTime) = "String" Then StartTime = TimeValue(StartTime)
' EndTime = cll.Offset(, 4).Value
' If TypeName(EndTime) = "String" Then EndTime = TimeValue(EndTime)
For hr = StartTime To EndTime Step 1 / 24
Destn.Value = hr
Destn.NumberFormat = "hh:mm AM/PM"
Set Destn = Destn.Offset(1)
Next hr
Set LastTable = NewSht.ListObjects.Add(xlSrcRange, Destn.Offset(-1).CurrentRegion, , xlYes)
With LastTable
.TableStyle = "TableStyleMedium14"
.ShowTableStyleRowStripes = False
'.Unlist
End With

Next cll
If Not LastTable Is Nothing Then LastTable.Unlist
If Not NewSht Is Nothing Then NewSht.Columns("B:B").EntireColumn.AutoFit
End Sub

The end time is the current time rounded down to the nearest hour. If you want it rounded up to the nearest hour then change Floor_Math to Ceiling_Math.
I've had a go at some formatting by temporarily converting to listobjects (Tables) and then converting back to a plain ranges.
If a sheet already exists, what do you want to happen?

I'll stop there and wait to hear from you.


you are my savior :crying:
If a sheet already exists I will only have to update each tables

[p45cal]

If a sheet already exists I will only have to update each tables
That could be very convoluted. Is there going to be data in those pre-existing tables? If so what happens to that data? What if the pre-existing table is from another day when it was created at a later time of day?
I'm not going to be in a hurry to spend lots of time on this one!

[jazz2409]

That could be very convoluted. Is there going to be data in those pre-existing tables? If so what happens to that data? What if the pre-existing table is from another day when it was created at a later time of day?
I'm not going to be in a hurry to spend lots of time on this one!

There will be data on each table. The report will be ran every hour. The data that will be captured for each hour should stay there. And this report is used daily so there will be no data from a different day

[jazz2409]

Also how do I set EndTime to get the value of a specific cell? I tried changing your EndTime to this:


EndTime = ThisWorkbook.Sheets("Scrubber").Range("D1").Value

cell D1 in sheet Scrubber has a formula of
=ROUNDDOWN(Scrubber!$B$1*24,0)/24

It's not working..I need the EndTime to get the value of cell D1

[jazz2409]

And do you think it will be easier to just pivot the data then put them in the tables rather than use formulas? I noticed that one formula significantly slowed down your code

[p45cal]

It's not working..I need the EndTime to get the value of cell D1That should work. It depends what's in cell B1 though.

[p45cal]

And do you think it will be easier to just pivot the data then put them in the tables rather than use formulas? I noticed that one formula significantly slowed down your codePivot what data?
What formulae?

[jazz2409]

I have a sheet named Consolidated. It contains all data that I need to compute and segregate to each sub category.

So what happens in this report is this:

1) Get raw data from system
2) Dump to Excel Sheet
3) Process it and transfer to Consolidated Sheet
4) Compute for each Sub Category and segregate per hour per sub category
5) Repeat from 1 throughout the day

[jazz2409]

I'll attach a sample workbook in a bit

[jazz2409]

here's the sample workbook. with sample data

[p45cal]

re-instate one line:
EndTime = ThisWorkbook.Sheets("Scrubber").Range("D1").Value
If TypeName(EndTime) = "String" Then EndTime = TimeValue(EndTime)
should do it.

[jazz2409]

re-instate one line:
EndTime = ThisWorkbook.Sheets("Scrubber").Range("D1").Value
If TypeName(EndTime) = "String" Then EndTime = TimeValue(EndTime)
should do it.

Yes I tried that. It works but if it's 12PM, it only shows until 11AM

[p45cal]

Yes I tried that. It works but if it's 12PM, it only shows until 11AMChange to:

For hr = StartTime To EndTime + 0.0001 Step 1 / 24

[p45cal]

You've removed the penultimate line:
If Not LastTable Is Nothing Then LastTable.Unlist
which means that the last table created remains a listobject (Excel Table).

[p45cal]

On the consolidated sheet there are many rows for the same date/time/Lob/SubLob which you seem to want to put on one line in the new sheets. How are you summarising theose multiple rows?

[jazz2409]

Change to:

For hr = StartTime To EndTime + 0.0001 Step 1 / 24

Worked like a charm :)
We're using GMT. Is there a need to change anything for that?



You've removed the penultimate line:
If Not LastTable Is Nothing Then LastTable.Unlist
which means that the last table created remains a listobject (Excel Table).

Do I add this after


Next cll
If Not NewSht Is Nothing Then NewSht.Columns("B:B").EntireColumn.AutoFit
If Not LastTable Is Nothing Then LastTable.Unlist

End Sub

?

[p45cal]

Worked like a charm :)
We're using GMT. Is there a need to change anything for that?I have no idea.





Do I add this after

Next cll
If Not NewSht Is Nothing Then NewSht.Columns("B:B").EntireColumn.AutoFit
If Not LastTable Is Nothing Then LastTable.Unlist
End Sub?Yes

[jazz2409]

On the consolidated sheet there are many rows for the same date/time/Lob/SubLob which you seem to want to put on one line in the new sheets. How are you summarising theose multiple rows?

There is a sheet there called computation. It has the formula on how to compute for each column :)

Originally I do SUMIFS (on some columns, for example) based on Sub Category and the time, but if I am to pursue using looping statements then I think I can no longer do that because it significantly slowed down your code

[p45cal]

Save me some time by supplying a workbook with a couple of tables filled out with the formulas you're using.

[jazz2409]

Save me some time by supplying a workbook with a couple of tables filled out with the formulas you're using.

Sure.. However it might take a few hours..

[jazz2409]

here's the sample workbook 25780

[p45cal]

Could you address ALL these please:
I see the formulae refer to cells on the same sheet. Would it not be possible to get this data from the Consolidated sheet?
Are you expecting plain values in each output table in the end, or are you happy for formulae to remain?
When an update of new data comes in, will old values remain the same? I ask this last because a complete refresh/recreation would be much easier and more robust than adding new data below old data.

[jazz2409]

1. I honestly think that it's best to get the data from the consolidated sheet
2. I actually prefer plain values
3. The old values has to remain the same. The new data will only be added to the tables. However I prefer the complete recreation since all data will stay in the consolidated sheet so even if we recreate the sheets the data will still be the same for the previous hours.

[p45cal]

Your formulae don't take into account Lob and SubLob!
They will be considerably longer. In a bid to shorten them, several of them show "" when it's going to be zero, viz:
=IF(SUMIF(Consolidated!$C:$C,$B3,Consolidated!$P:$P)=0,"",SUMIF(Consolidated!$C:$C,$B3,Consolidated!$P:$P))
where the blue is a replica of the red.
In a bid to shorten the formulae, did you know that you can set the whole sheet to show zeroes as blanks in Options? (untick the box at Options|Advanced|Display Options for this worksheet|Show a zero in cells that have zero value).
This affects ALL the cells on that sheet. Code can do it (ActiveWindow.DisplayZeros = False). Can we use it?
[There is another way using cell formatting where we could format all cells in a table to hide zero values - up to you]

ps. you could have included Lob/SubLob considerations in your formulae.

[jazz2409]

Your formulae don't take into account Lob and SubLob!

Sorry I forgot to indicate that in my actual workbook, the first table is always the overall per LOB that's why the workbook I sent with a table that has formula doesn't take the LOB and Sub LOB into consideration.


In a bid to shorten the formulae, did you know that you can set the whole sheet to show zeroes as blanks in Options? (untick the box at Options|Advanced|Display Options for this worksheet|Show a zero in cells that have zero value).

Sorry I am really new to this, I am not aware that can be done.. But thank you I learned something new :)


This affects ALL the cells on that sheet. Code can do it (ActiveWindow.DisplayZeros = False). Can we use it?

Yes.


[There is another way using cell formatting where we could format all cells in a table to hide zero values - up to you]

Whichever you think is best. I really have no other idea besides the ="" :)

[p45cal]

Sorry I forgot to indicate that in my actual workbook, the first table is always the overall per LOB that's why the workbook I sent with a table that has formula doesn't take the LOB and Sub LOB into consideration.Your formulae don't take into account even LOB. Do I take it this table resides on its own sheet and does NOT take into account either Lob or SubLob?

[jazz2409]

No it's on the same sheet.


So my initial workbook filters the consolidated sheet per Main LOB first and then the filtered set of data are being copied and pasted to its respective sheet. After it has been copied and pasted to its respective sheet, the Table 1 says Overall that's why the formula only calculates everything without taking the Main LOB and Sub LOB into consideration.


Then Table 2 is where the Sub LOBs actually start. Like:




25782

So Main LOB, Sub LOB, and time will be taken into consideration starting the second table up to the last one.

[p45cal]

And do you think it will be easier to just pivot the data then put them in the tables rather than use formulas?Just addressing this for a moment as a possibility.
In the attached is a pivot table on Sheet9, which at first sight appears to give the correct answers.
I need you to check that it's giving the right results, in all the columns, for the various combinations of Lob and SubLob using the slicers or the dropdowns at the top of column B of Sheet9.
There's not much data in that file, but you can change the source of the Pivot table to a bigger data set to check more thoroughly. The headers need to be exactly the same (at least the ones that are used used in the Pivot).

[jazz2409]

Just addressing this for a moment as a possibility.
In the attached is a pivot table on Sheet9, which at first sight appears to give the correct answers.
I need you to check that it's giving the right results, in all the columns, for the various combinations of Lob and SubLob using the slicers or the dropdowns at the top of column B of Sheet9.
There's not much data in that file, but you can change the source of the Pivot table to a bigger data set to check more thoroughly. The headers need to be exactly the same (at least the ones that are used used in the Pivot).

Everything is correct except for Full AHT. Formula has to be =IF('OB Tasks'=0,'IB AHT'/'IB Tasks',('IB AHT'/'IB Tasks')+(('OB AHT'/'OB Tasks')* ('OB Tasks'/'IB Tasks')))

[p45cal]

That formula gives the same results as mine when looking at the dataset you provided on the Consolidated sheet.
Could you provide me with a bigger dataset so that I can compare more thoroughly?

[jazz2409]

Hello, here's your file. I added more data here.

[p45cal]

Yes, all values are the same.
All these formulae give the same result:
Your formula:
=IF('OB Tasks'=0,'IB AHT'/'IB Tasks',('IB AHT'/'IB Tasks')+(('OB AHT'/'OB Tasks')* ('OB Tasks'/'IB Tasks')))
Your formula shortened:
=IF('OB Tasks'=0,'IB AHT'/'IB Tasks',('IB AHT'/'IB Tasks')+('OB AHT'/'IB Tasks'))
Your formula further shortened:
='IB AHT'/'IB Tasks'+IF('OB Tasks'=0,0,'OB AHT'/'IB Tasks')
my formula:
=' IB AHT'+' OB AHT'*'OB Tasks'/'IB Tasks'
Note that some of these references have leading spaces (' IB AHT' & ' OB AHT'); they are different from others ('IB AHT' & 'OB AHT') and refer to other calculated fields in the pivot, so my last formula, although the shortest, is not necessarily more efficient nor the best.

Now another scenario discovered on your larger data set which might need to be catered for:
SubLOB Category 1b, Jan 10th 2020, at 6:00,12:00,16:00 & 20:00 have all zero values for IB Tasks (column K of the Consolidated sheet), while all have non-zero values for OB Tasks (Column P).
They're causing errors at the moment. Do they need dealing with?

See attached, Sheet9, columns L:O for comparison.

[p45cal]

(I've made several edits to my last message as well as attaching a file.)

[jazz2409]

I'll check it in a bit. Also what error were you getting?

[p45cal]

Also what error were you getting?In the sheet I attached in the pivot choose category 1b:
25788
bedtime here.

[Paul_Hossler]

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

25789

[jazz2409]

In the sheet I attached in the pivot choose category 1b:
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

[jazz2409]

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

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

[jazz2409]

Do I create a Pivot first then the loop to create sheets and tables?

[p45cal]

Give me some time… I'm not full-time on this.

[Paul_Hossler]

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

[p45cal]

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?
25796

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
?

[p45cal]

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)

[jazz2409]

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 :(


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?
25796

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.


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

[jazz2409]

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?

[p45cal]

That formula is correct.
I'm not sure which formula…
Full AHT2 (15669 in cell M5 of the picture) is correct?

[p45cal]

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.

[jazz2409]

If OB AHT = "" or 0, the value of Full AHT is IB AHT/IB Tasks

I meant this formula

[jazz2409]

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))

[p45cal]

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?

[jazz2409]

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.


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.

[p45cal]

Attached

[jazz2409]

Attached

You are amazing works like a charm :bow::bow:
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 :bow:

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 :)

[jazz2409]

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?

[jazz2409]

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

[p45cal]

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?

[jazz2409]

I need to add another table at the end of the last sub category's table.

[p45cal]

The last snippet I gave adds a table after the last sub category on every category (LOB) sheet.
To get one table after the last sub category on only the last category sheet, put your code just before End Sub, say in the vicinity of:
NewSht.Cells(Rows.Count, "B").End(xlUp).Offset(2)

[jazz2409]

The last snippet I gave adds a table after the last sub category on every category (LOB) sheet.
To get one table after the last sub category on only the last category sheet, put your code just before End Sub, say in the vicinity of:
NewSht.Cells(Rows.Count, "B").End(xlUp).Offset(2)

Yes, I got it working already however there are columns on the last table that I need merged

[p45cal]

there are columns on the last table that I need mergedYour file has an extra sheet new table, but no indication of what you want merged, nor any new code. It stumps me that you create a new table AND then want to merge columns, why not create a single column to hold the information you want in the first place?

[jazz2409]

Your file has an extra sheet new table, but no indication of what you want merged, nor any new code. It stumps me that you create a new table AND then want to merge columns, why not create a single column to hold the information you want in the first place?

Sorry I forgot to indicate on that sheet what needs to be merged *facepalm*
I need to merge cell B1 and C1, D1 and E1, and F1 and G1

[p45cal]

Merged cells and vba are notoriuosly difficult to work with in VBA although your requirement here is quite simple and probably trouble-free it's infinitely preferable to use Centre-across-Selection, achievable by selecting the 2 cells, going into Format cells…, Alignment tab and choosing Centre Across Selection in the Horizontal: field.
In code that translates to the likes of:
Range("B1:C1").HorizontalAlignment = xlCenterAcrossSelection
Range("D1:E1").HorizontalAlignment = xlCenterAcrossSelection
Range("F1:G1").HorizontalAlignment = xlCenterAcrossSelection
although in the context of creating a new table in code, these may not be the actual cells involved.

[jazz2409]

I've been locked out of my NT login at work and couldn't login to my work laptop :omg2: I will try this as soon as the IT department has fixed it :omg2:

[jazz2409]

Okay so I was able to put the formula for the columns for the last table I was trying to put but I can't make it look like the table on the file I previously attached.

[p45cal]

I can't make it look like the table on the file I previously attached.Well, I have to throw it back over to you; how did you make that table look like it looks?!
Perhaps record a macro of you making the table look as you want it? Otherwise describe the steps you took here.

[jazz2409]

Here's the code I added:



TableHeaders1 = Array(".", "IB AHT", ".", "OB AHT", ".", "Full AHT", ".")
TableHeaders2 = Array("Hourly Table", "IB > 90 days", "IB < 90 days", "OB > 90 days", "OB < 90 days", "FAHT > 90 days", "FAHT < 90 days")


If Cll.Offset(1).Value <> CurrentCat Then NewSht.Cells(Rows.Count, "B").End(xlUp).Offset(2).Select
'Create the Tenure Table here:
Set Destn = NewSht.Cells(Rows.Count, "B").End(xlUp).Offset(2)
'optionally convert table to plain range (next line only):
If Not LastTable Is Nothing Then LastTable.Unlist
With Destn
.Value = CurrentCat & " Tenure-Wise Summary"
With .Font
.Name = "Calibri"
.Size = 11
.Underline = xlUnderlineStyleSingle
.Bold = True
End With
End With
Set Destn = Destn.Offset(2)
'Destn.Resize(, 7).Value = TableHeaders1
'Set Destn = Destn.Offset(1)
Destn.Resize(, 7).Value = TableHeaders2
Set Destn = Destn.Offset(1)
StartTime = Cll.Offset(, 3).Value
If TypeName(StartTime) = "String" Then StartTime = TimeValue(StartTime)
For hr = StartTime To EndTime + 0.0001 Step 1 / 24
Destn.Value = hr
Destn.NumberFormat = "hh:mm AM/PM"
Set Destn = Destn.Offset(1)
Next hr
Set LastTable = NewSht.ListObjects.Add(xlSrcRange, Destn.Offset(-1).CurrentRegion, , xlYes)
With LastTable
.TableStyle = "TableStyleMedium14"
.ShowTableStyleRowStripes = False
'add TENURE-WISE formulae here.
On Error Resume Next
.ListColumns("IB > 90 days").DataBodyRange.FormulaR1C1 = "=IFERROR(SUMIFS(Consolidated!C12,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure > 90 days"",Consolidated!C4,""" & CurrentCat & """)/SUMIFS(Consolidated!C11,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure > 90 days"",Consolidated!C4,""" & CurrentCat & """),0)"
.ListColumns("IB < 90 days").DataBodyRange.FormulaR1C1 = "=IFERROR(SUMIFS(Consolidated!C12,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure < 90 days"",Consolidated!C4,""" & CurrentCat & """)/SUMIFS(Consolidated!C11,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure < 90 days"",Consolidated!C4,""" & CurrentCat & """),0)"
.ListColumns("OB > 90 days").DataBodyRange.FormulaR1C1 = "=IFERROR(SUMIFS(Consolidated!C17,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure > 90 days"",Consolidated!C4,""" & CurrentCat & """)/SUMIFS(Consolidated!C16,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure > 90 days"",Consolidated!C4,""" & CurrentCat & """),0)"
.ListColumns("OB < 90 days").DataBodyRange.FormulaR1C1 = "=IFERROR(SUMIFS(Consolidated!C17,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure < 90 days"",Consolidated!C4,""" & CurrentCat & """)/SUMIFS(Consolidated!C16,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure < 90 days"",Consolidated!C4,""" & CurrentCat & """),0)"
.ListColumns("FAHT > 90 days").DataBodyRange.FormulaR1C1 = "=IFERROR(IF([@[OB > 90 days]]="""",[@[IB > 90 days]],[@[IB > 90 days]]+[@[OB > 90 days]]*SUMIFS(Consolidated!C16,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure > 90 days"",Consolidated!C4,""" & CurrentCat & """)/SUMIFS(Consolidated!C11,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure > 90 days"",Consolidated!C4,""" & CurrentCat & """)),0)"
.ListColumns("FAHT < 90 days").DataBodyRange.FormulaR1C1 = "=IFERROR(IF([@[OB < 90 days]]="""",[@[IB < 90 days]],[@[IB < 90 days]]+[@[OB < 90 days]]*SUMIFS(Consolidated!C16,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure < 90 days"",Consolidated!C4,""" & CurrentCat & """)/SUMIFS(Consolidated!C11,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure < 90 days"",Consolidated!C4,""" & CurrentCat & """)),0)"
.ListColumns("Hourly Table").DataBodyRange.NumberFormat = "hh:mm AM/PM"
Range(.ListColumns(2).DataBodyRange, .ListColumns(7).DataBodyRange).NumberFormat = "0;-0;;@"
'convert to plain values:
'.DataBodyRange.Value = .DataBodyRange.Value
End With
End If

The headers on TableHeaders2 aren't really supposed to be like that, I just don't know how to make the table look like the one on the Excel file I previously posted. The code above works however the table isn't supposed to look like that

[p45cal]

I'm in a rush, just going out...
the code you've given seems to show what doesn't work; what I'm asking is how you made that table look like it does: what formatting did you apply to make it like that? Record a new macro of you creating that table and how it looks, and post that entirely new code here. Sorry, gotta go. (A few hours)

[jazz2409]

I'm in a rush, just going out...
the code you've given seems to show what doesn't work; what I'm asking is how you made that table look like it does: what formatting did you apply to make it like that? Record a new macro of you creating that table and how it looks, and post that entirely new code here. Sorry, gotta go. (A few hours)

I created the table from scratch manually :(

[p45cal]

I created the table from scratch manually :(Yes! Do it again while recording a macro and post the code here.

[jazz2409]

Here's the code generated by the recorder. Please note that table headers and time under hourly table are pre-typed



Sub Macro3()'
' Macro3 Macro
'


'
Range("I1:O2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.499984740745262
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("I3:O13").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = 0
.Weight = xlThin
End With
Columns("I:O").Select
Range("I2").Activate
Columns("I:O").EntireColumn.AutoFit
Columns("I:O").EntireColumn.AutoFit
ActiveWindow.ScrollRow = 1
Range("J1:K1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("L1:M1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("N1:O1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
End Sub

[p45cal]

That code boils down to:
Sub Macro3b()
With Range("I1:O2")
.Interior.Color = 2315831
.Font.Color = 16777215
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
With Range(Range("I3:O13"), Range("I3:O13").End(xlDown))
.Interior.Color = 11854022
With Intersect(.Cells, .Cells.Offset(, 1))
.HorizontalAlignment = xlCenter 'you didn't have this but you may want it
.VerticalAlignment = xlCenter 'you didn't have this but you may want it
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Color = 16777215
End With
Range("J1:K1").HorizontalAlignment = xlCenterAcrossSelection
Range("L1:M1").HorizontalAlignment = xlCenterAcrossSelection
Range("N1:O1").HorizontalAlignment = xlCenterAcrossSelection
.EntireColumn.AutoFit
End With
End Sub
Can you work with that?

[jazz2409]

Hmmm I am honestly not sure how to change the current code into that

[p45cal]

OK, let's have the current version of the workbook…

[jazz2409]

Here's my most recent workbook

[jazz2409]

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?

[p45cal]

Attached.

[p45cal]

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?

[jazz2409]

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

[p45cal]

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.)


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.

[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.

[p45cal]

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.

[jazz2409]

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..

[p45cal]

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:

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?

[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..

[p45cal]

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.

[jazz2409]

Hello, sorry I got word to not use the Sub LOB's site and use the Main LOB's site instead.

[p45cal]

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.

[jazz2409]

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 :)

[jazz2409]

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).

[p45cal]

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

[jazz2409]

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?

[p45cal]

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]

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.

[jazz2409]

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

[p45cal]

Hmm just please correct me if my understanding is wrong.

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


For Each pit In pf.PivotItems 'loops through everything that's in the array loops through the items in the dropdown of that field.

pf.CurrentPage = pit.Name 'selects specific item inside the arrayfilters that page field to one of the items.

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

[jazz2409]

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?

[p45cal]

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.

[jazz2409]

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.

[jazz2409]

By the way is there a way to remove irrelevant sub categories in the filter based on the selection in main LOB filter?

[Paul_Hossler]

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