VBA for selecting a varying chart data range in Excel 2007

Question

I am NOT a programmer!  I am writing a macro to create a pie chart in a worksheet.  The data dumps from another system into an Excel 2007 template which will contain the macro to create the pie chart.  The size of the data range (number of rows) will vary.  When I select the data range the macro records the specific cells rather than using the Shift+End+Down function to select a variable range.  I have looked and tried and I'm stumpped! 

What is the VBA language I need to use to replace the specific cell range reference that will work like the keystrokes Shift+End+Down to define my chart data range?

Answer 1

I wanted to provide a little more detail on what I've tried and what the results were.

Option 1:

Sheets("1-3").Select

    ActiveSheet.Shapes.AddChart.Select

    ActiveChart.ChartType = xl3DPieExploded

    ActiveChart.SeriesCollection.NewSeries

    ActiveChart.SeriesCollection(1).Name = "='1-3'!$A$4"

    ActiveChart.SeriesCollection(1).Values = "='1-3'!$W$6"

    Application.SendKeys ("{SHIFT}&{END}&{DOWN}")

    ActiveChart.SeriesCollection(1).XValues = "='1-3'!$S$6:$S$26"

    ActiveWindow.LargeScroll ToRight:=-1

    ActiveChart.Legend.Select

    ActiveSheet.ChartObjects("Chart 1").Activate

    Selection.Top = 38.272

    Selection.Height = 311.679

Error Message

It stops on the line with the SendKeys and provides the following error message:
Run-time error ‘1004’:

Method ‘SendKeys’ of object’_Application’failed

Option 2

Sheets("1-3").Select

    ActiveSheet.Shapes.AddChart.Select

    ActiveChart.ChartType = xl3DPieExploded

    ActiveChart.SeriesCollection.NewSeries

    ActiveChart.SeriesCollection(1).Name = "A4"

        Dim rng As Range

    Set rng = ActiveSheet.Range("W6", "W1000").End(xlUp)

    ActiveChart.SeriesCollection(1).Values = rng

  

Result

Ran without error but didn’t select the data range.  I tried including the sheet name before the cell references but it wouldn't run with that.

Any help would be greatly appreciated!!

Thanks!

Answer 2

Hi,

Try this code,

Sub MakeChart()

    Dim rngLabels As Range
    
    Sheets("1-3").Select
    Set rngLabels = Range("S6", Cells(Rows.Count, 19).End(xlUp))
    
    With ActiveSheet.Shapes.AddChart.Chart
        ' remove any series auotmatically created
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
        
        With .SeriesCollection.NewSeries
            .Name = "='1-3'!A4"
            .XValues = rngLabels
            .Values = rngLabels.Offset(0, 4)
            .ChartType = xl3DPieExploded
        End With
    End With
    
End Sub

---

Cheers www.andypope.info