VBA - Looping through all chart sheets and copying onto one worksheet

I am trying to loop through all the charts generated in my workbook which exist on the 2nd sheet to the 2nd to last sheet. I want to loop through all the charts, copy the chart area and paste it onto the last sheet which has data (just a regular spreadsheet).

I can't see why the code below is not working but I end up with a Run-time error '438' - Object doesn't support this property or method on the first line (for loop).

Any ideas why this isn't working?

Sub chartCopy()

    For I = Sheets(2) To Sheets(ActiveWorkbook.Sheets.Count - 1)

        ActiveChart.ChartArea.Copy
        Sheets(Sheets.Count).Select
        ActiveSheet.Paste

    Next I


End Sub

Answers


why it isn't working is pretty easy: You try to count from Sheet(2) (which is án object) to the number of sheets -1. Of course this wont work, since sheets(2) is an object. Try For I = 2 To Sheets(ActiveWorkbook.Sheets.Count - 1) which will start with 2. Even if you want to use the amount of sheets you give the index number of the starting sheet, not the sheet itself.


Need Your Help

Query datastore for the set of property values present

google-app-engine properties gae-datastore set

I have a property column which can have a subset of the following values at any point in time: { a | b | c | d | e }. By this, I mean that sometimes it can be any of { a | d | e }, or at another ti...