creating multiple radar graph from multiple column from single worksheet

[prashantkochar263]

I worksheet of 500+ column. I need to create radar 1 graph for each 20 column.Each column has 72 rows.means obviously 1 radar for 1 column. each chart has 20 columns/ radars.
from 1 to 20 column 1 chart will be there next chart is for 21 to 40 column. 3rd chart for 41 to 60 column and so on.. suppose if i have 400 sheets then charts will be 20.
i am new to VBA. and this is urgently required.

thanks..

 

[shknbk2]

You can try something like this. Each chart is placed at the start of the 20-set (chart1 is at A2, chart2 is at U2, etc.). You can change where they are placed by modifying the rows in the code with ChartArea. If you need further help, let me know.

Sub createCharts()
    Dim numCols As Integer
    Dim chartColStart As Integer, chartColEnd As Integer
    
    chartColStart = -19
    numCols = Cells(1, Columns.Count).End(xlToLeft).Column
    Do
        If numCols > chartColStart Then
            chartColStart = chartColStart + 20
            chartColEnd = chartColStart + 19
            If numCols < chartColEnd Then chartColEnd = numCols
            ActiveSheet.Shapes.AddChart2(-1, xlRadar).Select
            Dim ch As Chart
            Set ch = ActiveChart
            ch.SetSourceData Source:=Range(Cells(1, chartColStart), Cells(72, chartColEnd))
            ch.ChartArea.Left = Columns(chartColStart).Left
            ch.ChartArea.Top = Rows(2).Top
            ActiveSheet.Range("A1").Select
        End If
    Loop While chartColEnd < numCols
End Sub

 

[prashantkochar263]

Thanks ... but there is a problem... for first its crating chart for 20 column after that its creating for 72 columns .... each time.. so help required...

 

[prashantkochar263]

for first 20 its fine .. i checked for 2nd and later it's showing data to be =Sheet2!$U$1:$AN$1 but it should be Sheet2!$U$1:$U$72......... send the updated one........

 

[shknbk2]

I thought you wanted charts in groups of 20 columns. Like you said, the second chart would be for columns 21-40. $U$1:$AN$72 is columns 21-40.

 

[prashantkochar263]

in second chart series should be 21 to 40 so series 21 should be $U$1:$U$72 ......... 22 Series will be $V$1:$V72$ .....LIKE THAT... LAST40 Series will be $AN$1:$AN$72........... Remember for plotting 1 radar we we are using 1 column......

 

[shknbk2]

Okay. Try this:

Sub createCharts()
    Dim numCols As Integer
    Dim chartColStart As Integer, chartColEnd As Integer
    
    chartColStart = -19
    numCols = Cells(1, Columns.Count).End(xlToLeft).Column
    Do
        If numCols > chartColStart Then
            chartColStart = chartColStart + 20
            chartColEnd = chartColStart + 19
            If numCols < chartColEnd Then chartColEnd = numCols
            ActiveSheet.Shapes.AddChart2(-1, xlRadar).Select
            Dim ch As Chart
            Set ch = ActiveChart
            ch.SetSourceData Source:=Range(Cells(1, chartColStart), Cells(72, chartColEnd))
            ch.PlotBy = xlColumns
            ch.ChartArea.Left = Columns(chartColStart).Left
            ch.ChartArea.Top = Rows(2).Top
            ActiveSheet.Range("A1").Select
        End If
    Loop While chartColEnd < numCols
End Sub

 

[prashantkochar263]

thanks... buddy... it's working absolutely fine....... you made my day... thanks a lot.........have a great day........

 

[shknbk2]

No problem. Sorry I wasn't understanding you on this last part. Glad it all works now.

By the way, in testing the code, it was tedious to delete all billion charts I created across many columns by hand, so I created the quicker code below. Run it if you want to delete all charts and start over.

Sub deleteShapes()
    For Each s In ActiveSheet.Shapes
        s.Delete
    Next
End Sub