Creating multiple graphs from each column using VBA

[forensic93]

Hi,

I have attempted at creating a new chart for each column of data that i have. The first column data isn't needed so i've started from column B to I.
My code however doesn't seem to like the loop i have created.

If anyone could see what i've done wrong that would be appreciated.

Sub CreateChart()

Application.ScreenUpdating = False

Dim LastRow As Long
Dim xTitle As Range
Dim xData As Range
Dim yTitle As Range
Dim yData As Range
Dim column As Integer

'Find last row with data
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

For column = 2 To 9
    'Set x-axis title
    Set xTitle = Range("A1")

    'Set y-axis title and data
    Set yTitle = Range(column, 1)
    Set yData = Range(column, LastRow)

    'set total graph range
    Set GraphRange = Union(yTitle, yData)

    'create chart
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.ChartType = xlLine
        ActiveChart.SetSourceData Source:=GraphRange
        ActiveChart.Location Where:=xlLocationAsNewSheet
        ActiveChart.SetElement (msoElementLegendNone)
        ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
        Selection.Caption = xTitle
        ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
        Selection.Caption = yTitle
       
Next column
   
Application.ScreenUpdating = True

End Sub

 

[pgc01]

Hi

    'set total graph range
    Set GraphRange = Union(yTitle, yData)

This creates a range with 2 cells. For ex., if LastRow is 10 and column is B you get the range with the cells B1 and B10.
Is this what you want or do you want the range B1:B10?

If that's the case use

Set GraphRange = Range(yTitle, yData)

 

[forensic93]

If that's the case use

Set GraphRange = Range(yTitle, yData)

Hi,

The code works to create one graph if i pull it out of the loop.
My issue is purely just that when i put it in the loop i get an error for using the variable column to define which column i'm trying use. The section of code i'm referring to i've posted below

  'Set y-axis title and data
    Set yTitle = Range(column, 1)
    Set yData = Range(column, LastRow)

 

[pgc01]

I see. Range expects range parameters.
If you want to define the range with column and row number then use Cells

  'Set y-axis title and data
    Set yTitle = Cells(column, 1)
    Set yData = Cells(column, LastRow)

.