Macro / VBA help

[vharp91]

I recorded a macro that inserts a chart on the same sheet that has the data. My intention is to be able to run this same macro on subsequent sheets, however, when I try to run it on other sheets it just keeps going back to the first sheet to insert the chart. I have pasted the VBA macro code below. Can someone tell me how to correct it so that is will run on other sheets I select rather than just the one I recorded the macro on. Thank you!

Sub Insertchart()
'
' Insertchart Macro
'
' Keyboard Shortcut: Ctrl+a
'
If Range("A3").Value <> "Position" Or Range("A4").Value = "" Then
Exit Sub
Else
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("A1").Value
    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(1).AxisGroup = 1
    ActiveChart.FullSeriesCollection(2).ChartType = xlLine
    ActiveChart.FullSeriesCollection(2).AxisGroup = 1
    ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.5
        .Transparency = 0
        .Solid
    End With
    ActiveChart.FullSeriesCollection(2).Select
    ActiveChart.FullSeriesCollection(1).Select
    ActiveChart.FullSeriesCollection(1).ApplyDataLabels
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    ActiveChart.Legend.Select
    ActiveChart.Legend.LegendEntries(2).Select
    ActiveChart.ChartArea.Select
    ActiveChart.Legend.Select
    ActiveChart.Legend.Select
    Selection.Position = xlTop
End If
End Sub

 

[Logit]

Where have you pasted that macro ?

 

[Peter_SSs]

@vharp91
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block at the bottom of this post has more details. I have added the tags for you this time.

 

[Logit]

Paste the following into a Regular module :

Option Explicit


Sub Insertchart()
'
' Insertchart Macro
'
' Keyboard Shortcut: Ctrl+a
'
    Dim chartRange As Range
    Dim chartObj As ChartObject
    Dim ws As Worksheet

    ' Set the active worksheet
    Set ws = ActiveSheet

    ' Disable screen updating and events
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    On Error GoTo Cleanup ' Ensure resources are re-enabled if an error occurs

    ' Check the conditions in A3 and A4
    If ws.Range("A3").Value <> "Position" Or ws.Range("A4").Value = "" Then
        MsgBox "Conditions not met in A3 or A4. Exiting macro."
        Exit Sub
    End If

    With ws
        ' Define the full data range explicitly
        Set chartRange = .Range("A3:D7")

        ' Add the chart and set its position
        Set chartObj = .ChartObjects.Add(Left:=.Range("A7").Left, _
                                         Top:=.Range("A7").Top + 50, _
                                         Width:=400, Height:=300)
        ' Configure the chart
        With chartObj.Chart
            .SetSourceData Source:=chartRange
            .ChartType = xlColumnClustered

            ' Minimal formatting for speed
            If .SeriesCollection.Count > 0 Then
                .SeriesCollection(1).ApplyDataLabels
            End If
        End With
    End With

Cleanup:
    ' Re-enable screen updating and events
    Application.ScreenUpdating = True
    Application.EnableEvents = True

    
End Sub

As you can see from the attached image, the chart is dependent on data located in cells : A3:D7. To utilize a different range of cells it is necessary
to edit the macro accordingly.