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

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

pf 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 array

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