CODEWITHSUNDEEP
vbaexcel
Sparta
Sparta

Reputation: 35

Loop through excel tables and create graphs

I have multiple tables in one worksheet and I need to loop through tables(list objects) and generate corresponding line graphs. I tried using for each loop and it is not working: How can I use 'for each' loop to generate graphs? How to reference each list object as a range to my graphs?

Sub chart_create()

Dim tbl As listobject

'Loop through each sheet and table in the workbook

    For Each tbl In ActiveSheet.ListObjects         
     Call graph
  End Sub

    Next tbl

End Sub

'macro to generate charts 

Sub graph()

    Dim rng As Range
    Dim cht As ChartObject
 'how do i change this to reference corresponding list object 
    Set rng = Selection   

    Set cht = ActiveSheet.ChartObjects.Add( _
        Left:=ActiveCell.Left, _
        Width:=450, _
        Top:=ActiveCell.Top, _
        Height:=250)

    'Give chart some data
    cht.Chart.SetSourceData Source:=rng

    'Determine the chart type
    cht.Chart.ChartType = xlLine

End Sub

Upvotes: 0

Views: 537

Answers (2)

TFrazee
TFrazee

Reputation: 807

First, it looks like you have an extra End Sub in there. Next tbl must come before End Sub or else it will never be reached.

Second, you need to pass a reference to your table into the graphing function.

Sub chart_create()

    Dim tbl As listobject

    'Loop through each sheet and table in the workbook

    For Each tbl In ActiveSheet.ListObjects         
        Call graph(tbl)
    Next tbl
End Sub

And then...

Sub graph(tbl As ListObject)
    'Make your graph here, referencing the tbl you passed in
End Sub

Edit: Lastly, just to be clear, your comment says that you're "looping through each sheet and table in the workbook," but you're actually just looping through listobjects on the active worksheet. If you want to loop through each worksheet, you'll need to have an extra loop outside the existing loop like:

For Each ws In Worksheets
    'For Each tbl In ws.ListObjects....
Next ws

Upvotes: 1

JNevill
JNevill

Reputation: 50019

Pass the Range for the ListObject into your second subroutine as a parameter:

Sub chart_create()

  Dim tbl As listobject

  'Loop through each sheet and table in the workbook
  For Each tbl In ActiveSheet.ListObjects         
     Call graph tbl.Range
  Next tbl

End Sub

'macro to generate charts
Sub graph(rng as range)     
    Dim cht As ChartObject

    Set cht = ActiveSheet.ChartObjects.Add( _
        Left:=ActiveCell.Left, _
        Width:=450, _
        Top:=ActiveCell.Top, _
        Height:=250)

    'Give chart some data
    cht.Chart.SetSourceData Source:=rng

    'Determine the chart type
    cht.Chart.ChartType = xlLine

End Sub

Upvotes: 1

Related Questions