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

Question

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

Answer 1

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.