CODEWITHSUNDEEP
excelvba
Fabrizio Bianchi
Fabrizio Bianchi

Reputation: 49

How to refer macro to cell range in current sheet?

I recorded a macro to create graphs on a worksheet. The data are organized in the same way in all the sheets of the workbook, therefore I would like to generalize the macro so that it can be used on every sheet (or if it is possible to batch through the worksheets).

The code looks like this:

ActiveWindow.SmallScroll Down:=-57
Range("C5:C65").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Range("fr_1!$C$5:$C$65")
ActiveChart.Axes(xlCategory).Select
ActiveChart.SeriesCollection(1).XValues = "=fr_1!$A$5:$A$65"

Having recorded the macro on fr_1 I now have that reference in the 5th and last line, while I would like to have a general reference to the active sheet.

How do I do this?

Upvotes: 1

Views: 5461

Answers (2)

brettdj
brettdj

Reputation: 55672

You can

  • add the desired sheets to an Array and just access the sheets you want to place charts on. This code only runs on the three sheet names supplied by Arrshts = Array("Sheet1", "Sheet3", "MySheet With Space")
  • skip the potential naming issue by referring just to local ranges

[Updated - added error handling for potential invalid sheet names]

    Sub Sample()
    Dim ws As Worksheet
    Dim Arrshts()
    Dim ArrSht
    Dim strOut As String
    Arrshts = Array("Sheet1", "Sheet3", "MySheet With Space")
    For Each ArrSht In Arrshts
    On Error Resume Next
    Set ws = Nothing
    Set ws = Sheets(ArrSht)
    On Error GoTo 0
    If Not ws Is Nothing Then
        With Sheets(ArrSht).Shapes.AddChart.Chart
            .ChartType = xlLine
            .SetSourceData Range("$C$5:$C$65")
            .SeriesCollection(1).XValues = Range("$A$5:$A$65")
        End With
    Else
        strOut = strOut & (vbNewLine & ArrSht)
    End If
    Next
    If Len(strOut) > 0 Then MsgBox strOut, , "These array names are incorrect and need adjusting"
    End Sub

Upvotes: 2

Alex K.
Alex K.

Reputation: 175748

You can:

Dim aSheet As Worksheet
For Each aSheet In ActiveWorkbook.Worksheets
    With aSheet.Shapes.AddChart.Chart
        .ChartType = xlLine
        .SetSourceData Source:=aSheet.Range(aSheet.Name & "!$C$5:$C$65")
        .SeriesCollection(1).XValues = "=" & aSheet.Name & "!$A$5:$A$65"
    End With
Next

If you want to iterate the manually selected sheets change to for each asheet in activewindow.selectedsheets

To manually filter by name;

Dim aSheet As Worksheet
For Each aSheet In ActiveWorkbook.Worksheets
    select case aSheet.name
        case "sheet1", "sheet50", "sheet999"   
            With aSheet.Shapes.AddChart.Chart
                .ChartType = xlLine
                .SetSourceData Source:=aSheet.Range(aSheet.Name & "!$C$5:$C$65")
                .SeriesCollection(1).XValues = "=" & aSheet.Name & "!$A$5:$A$65"
            End With
    end select
Next

Upvotes: 2

Related Questions