Reference the first sheet 1 (in index form) when creating a chart

Question

I'm trying to create a macro to rearrange and graph data.

I will be applying it to thousands of files that have different tab names based on the file name. I figured out that one may reference the tab via its 'Index Number' and that has worked when sorting the data.

Now that I am trying to create a line graph, I do not know how to reference the tab name. I'm trying to select the range. I changed it back to 'Sheet1' and was planning on changing the tab name of each file to 'Sheet1' before I learned of 'Index Number'.

Chart creating portion of the code:

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet1'!$Z$2:$AA$25")
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Sheet1'!$C$1"
ActiveChart.SeriesCollection(1).Values = "='Sheet1'!$AA$2:$AA$25"
ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!$Z$2:$Z$25"
ActiveChart.ApplyLayout (3)

I'm assuming that I want to replace all of the 'Sheet1's with some sort of Index Number. I tried random combinations of Sheets(1).

Answer 1

Amanda,

I have modified your code to reference the first sheet in a workbook by it's index number. This code will allow you to apply the chart to the very first sheet in your workbook regardless of what it has been named. This is accomplished with sheets(x) where "x" is the sheet you want to reference. The first sheet in your workbook is traditionally "1".

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Sheets(1).Range("Z2:AA25")
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = Sheets(1).Range("C1")
ActiveChart.SeriesCollection(1).Values = Sheets(1).Range("AA2:AA25")
ActiveChart.SeriesCollection(1).XValues = Sheets(1).Range("Z2:Z25")
ActiveChart.ApplyLayout (3)

Hope this helps,

John

Answer 2

This will work. Your code contained a lot of repetitious keywords; notice that I cleaned it up using the With statement. Also everything is specified at the top of the code: where the data is located, etc.

It is poor practice to rely on the index of a Sheet or SeriesCollection, blindly assuming that it's (1). I guarantee that this will screw things up for you at some point. Better to keep references to these objects, as done below, e.g. Set serMyseries = .SeriesCollection.NewSeries and then operate on serMyseries -- not on SeriesCollection(1).

All the Dim statements are only required if you have Option Explicit at the top of your code. If you don't use Option Explicit, then those lines can be deleted. (But Option Explicit is good practice.)

Dim shtData As Worksheet
Dim chtMyChart As Chart
Dim serMyseries As Series
Dim rngName As Range
Dim rngMyValues As Range
Dim rngMyXValues As Range

' What sheet is the data on?
Set shtData = Worksheets("Sheet1") ' Or whatever it's called on the tab

' Where is the data?
Set rngName = shtData.Range("C1")
Set rngMyValues = shtData.Range("AA2:AA25")
Set rngMyXValues = shtData.Range("Z2:Z25")

With shtData
    .Activate

    ' Add a chart.
    Set chtMyChart = .Shapes.AddChart
    With chtMyChart
        .ChartType = xlLineMarkers
        ' Add the series.
        Set serMyseries = .SeriesCollection.NewSeries
        With serMyseries
            .Name = rngName
            .Values = rngMyValues
            .XValues = rngMyXValues
        End With
        .ApplyLayout (3)
    End With
End With

The following two lines of code were useless (first line creates a series, second line deletes it immediately) so I removed them.

ActiveChart.SetSourceData Source:=shtData.Range("Z2:AA25")
ActiveChart.SeriesCollection(1).Delete