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
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.