Code for making chart sheet with varying range

[mdlc805]

im having trouble with making a code that will extract data out of a sheet that ill have a varying range and turning it into a chart sheet. can someone help me out please or point me in the write direction.
tried this but dont know how to vary range
Sub test1()
Dim test As Chart
Set test = Charts.Add




End Sub


also tried this but dont know how to make it to chart sheet

Sub Macro1()




ActiveSheet.Shapes.AddChart2(227, xlLine).Select
Range(Range("B2"), Range("B2").End(xlDown)).Select





End Sub

 

[Domenic]

Re: need help with code for making chart sheet with varying range

Let's assume that Column A and Column B contain the source data for the chart, and that the sheet containing the data is the active sheet, try the following macro...

Option Explicit

Sub CreateChart()


    Dim theSourceRange As Range
    Dim theChart As Chart
    Dim lastRow As Long
    
    'get the last used row in Column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    'set the source data for the chart
    Set theSourceRange = Range("A1:B" & lastRow)
    
    'create a chart sheet and place it at the beginning of the workbook
    Set theChart = Charts.Add(Before:=Sheets(1))
    
    'set some properties for the chart
    With theChart
        .ChartType = xlLine
        .SetSourceData theSourceRange
        .SetElement msoElementChartTitleAboveChart
        .ChartTitle.Text = "My Chart Title"
        .SetElement msoElementLegendBottom
    End With
    
End Sub

Hope this helps!

 

[mdlc805]

Re: need help with code for making chart sheet with varying range

This is the code I wrote that seems to be working but I cant figure out how to add multiple data sets to each charts.

Sub test()
Dim test As Chart
Set test = Charts.Add
With test
test.SetSourceData Source:=Sheets("Sheet1").Range("G5").EntireColumn
.ChartType = xlLine
.HasTitle = True
.ChartTitle.Text = "test"






End With
Sheets(1).Name = "test"


Dim test1 As Chart
Set test1 = Charts.Add
With test1
test.SetSourceData Source:=Sheets("Sheet1").Range("Z5").EntireColumn
.ChartType = xlLine
.HasTitle = True
.ChartTitle.Text = "test1"




End With
Sheets(1).Name = "test1"
End Sub

 

[Domenic]

Re: need help with code for making chart sheet with varying range

Can you please clarify whether you want to create one chart or whether you want to create one or more charts? And, can you please specify where the data is located for your chart or charts?

 

[mdlc805]

Re: need help with code for making chart sheet with varying range

Can you please clarify whether you want to create one chart or whether you want to create one or more charts? And, can you please specify where the data is located for your chart or charts?

I need to create multiple charts with each chart having multiple data sets. Then I need to move all charts to a new single chart sheet and it needs to be organized.