CODEWITHSUNDEEP
vbaexcel
Nithin Shettigar
Nithin Shettigar

Reputation: 33

To generate scatter xy graph in excel using macro

I have written a macro to generate graph in excel but i want that graph to be generated in the next sheet not in the same sheet.i have pasted my macro below its working properly i just want it to be in the next sheet. please provide me some solution* 

Sub LumData1()

    If IsEmpty(Range("B2,D2")) = False Then
        Range("B:B,D:D,H:H,I:I,J:J,M:M").Select
        Range("M1").Activate
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
        ActiveChart.SetSourceData Source:=Sheets(ActiveSheet.Name).Range( _
            "$B:$B,$D:$D,$H:$H,$I:$I,$J:$J,$M:$M" _
            )
        ActiveChart.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name

        With ActiveChart.ChartArea
            .Width = 1060
            .Height = 420
            .Left = 0
        End With

        ActiveChart.SeriesCollection(1).Select
        ActiveChart.SeriesCollection(1).AxisGroup = 2
        ActiveChart.SeriesCollection(5).Select
        ActiveChart.SeriesCollection(5).AxisGroup = 2
        ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
        ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
        ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
        ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Value"
        ActiveChart.Axes(xlCategory).TickLabels.Orientation = 45
    End If

End Sub

Upvotes: 0

Views: 599

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

My answer covers not only how to solve your question in your post, but how to automatically define and set-up charts in VBA in a “cleaner” and safer way.

It's better if you avoid using ActiveSheet, Select, and ActiveChart. Instead always use referenced objects, in this case Worksheets and ChartObject. E.g Dim ChtObj As ChartObject , and later set it with Set ChtObj = ShtCht.ChartObjects.Add(100, 100, 500, 500) , where ShtCht is the worksheet where you create your chart.

Once you defined and set your ChartObject, its quite easy to modify its properties by using the With ChtObj , and also nested properties, by adding With .Chart.ChartArea under the first With statement.

Code

Option Explicit

Sub LumData1()

Dim ChtObj As ChartObject
Dim ShtSrc  As Worksheet
Dim ShtCht  As Worksheet

' change "Sheet1" to your sheet's name (where you have your chart's data)
Set ShtSrc = Worksheets("Sheet1") ' <-- I prefer not to work with ActiveSheet
 
'Set ShtCht = Worksheets("Sheet2") ' <-- set the chart's destination worksheet
Set ShtCht = Worksheets.Add ' <-- create a new worksheet to place the chart
ShtCht.Name = "Chart"

If Not IsEmpty(ShtSrc.Range("B2,D2")) Then

    Set ChtObj = ShtCht.ChartObjects.Add(100, 100, 500, 500)
    
    With ChtObj
        .Chart.ChartType = xlXYScatterSmoothNoMarkers
        .Chart.SetSourceData ShtSrc.Range("$B:$B,$D:$D,$H:$H,$I:$I,$J:$J,$M:$M")
                
        ' set position of the chart to Cell M1
        .Top = ShtCht.Range("M1").Top
        .Left = ShtCht.Range("M1").Left
        
        ' modify chart position and dimensions
        With .Chart.ChartArea
            .Width = 1060
            .Height = 420
            .Left = 0
        End With
        
        With .Chart
            .SeriesCollection(1).AxisGroup = 2
            .SeriesCollection(5).AxisGroup = 2
        
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Value"
            .Axes(xlCategory).TickLabels.Orientation = 45
        End With
    
    End With

End If

End Sub

Upvotes: 1

user3598756
user3598756

Reputation: 29421

edited after OP's clarification he has to make a new sheet to host the chart into

to stick to your code as much as possible:

  • remove ActiveChart.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name

  • place the following code right before End if

    Dim myChart As Chart: Set myChart = ActiveChart
Worksheets.Add
myChart.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name

Upvotes: 0

Related Questions