Help with loops to create pivot tables and arrange graphs in different orders

serrulate

New Member
Joined
May 18, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hello,

i'm looking for help with creating a loop for creating pivot tables and also some help in defining how graphs are arranged.

The workbook i have works ok, however i am manually creating a pivot table for each Toy category in the data table. When more Toy categories are added i would like to have a loop that automatically creates a new pivot table for each category. For example, if a new toy category of Car is added to the data table, a pivot table would be created.

1666711172052.png

.
1666711207707.png


The second question is i would like to find a way to control the order of how the graphs appear, maybe arrange to have Cat then Sheep then Dog. here is my script for creating and arranging the graphs.

VBA Code:
Sub Chart_Creation()
'Chart Creation

Dim Tbl As ListObject, Cht As Object, Rng As Range, ws As Worksheet, ws2 As Worksheet, pt As PivotTable
Set ws2 = Sheets("Charts")
Set ws = Sheets("PIVTab")

ws2.ChartObjects.Delete

For Each pt In ws.PivotTables

    Set Cht = ws2.ChartObjects.Add(Left:=180, Width:=300, Top:=7, Height:=200)
    With Cht.Chart
        .SetSourceData Source:=pt.TableRange1
        .HasTitle = True
        .ChartTitle.Text = pt.TableRange1(2, 1)
        .ChartTitle.Font.Size = 10
        .Parent.Name = pt.TableRange1(2, 1)
        .FullSeriesCollection(1).ChartType = xlLine
        .FullSeriesCollection(2).ChartType = xlLineMarkers
        .ShowAllFieldButtons = False
        .Legend.Position = xlBottom
       ' .Legend.Width = 217.54
        '.Legend.Height = 25.878
        .Legend.Font.Size = 7
        .Axes(xlCategory).MajorTickMark = xlNone
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Font.Size = 8
        
     

    End With

Next pt
Arrange_Charts

End Sub

Sub Arrange_Charts()
' Arrange Charts into Grid

Dim int_cols As Integer
    int_cols = 3
    
    Dim cht_width As Double
    cht_width = 275

        
    Dim cht_height As Double
    cht_height = 225
    
    Dim offset_vertical As Double
    offset_vertical = 35
    
    Dim offset_horz As Double
    offset_horz = 4

    Dim sht As Worksheet
    Set sht = Sheets("Charts")

    Dim count, Chartcount, i, j As Integer
    count = 0
        
    Dim cht_obj As ChartObject
     Chartcount = sht.ChartObjects.count
    'iterate through ChartObjects on current sheet
 
   
    For Each cht_obj In sht.ChartObjects
         
        'use integer division and Mod to get position in grid
        cht_obj.Top = (count \ int_cols) * cht_height + offset_vertical
        cht_obj.Left = (count Mod int_cols) * cht_width + offset_horz
        cht_obj.Width = cht_width
        cht_obj.Height = cht_height

        count = count + 1
      
   
    Next cht_obj

End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,187,074
Messages
5,961,423
Members
438,544
Latest member
DrDoyle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top