Search code examples
excel-2010excel-2013

VBA Run Time error in Excel 2013 that works well in Excel 2010

I have a macro that we were using for years and worked well in Excel 2010. However, now that we upgraded to Excel 2013, it gives a run time error 9. The following code part is highlighted after debugging it: 

Sheets(1).Copy after:=Workbooks(mfname).Sheets(3)

I know already that this error has to do something with the availability of the range. The question is if there is any easy fix to this? The relevant code-snippet:

Workbooks.Add
mfname= ActiveWorkbook.Name

Workbooks.Open template
template_name = ActiveWorkbook.Name
Sheets(1).Copy after:=Workbooks(mfname).Sheets(3)

Workbooks(mfname).Activate
Sheets(1).Delete
Sheets(1).Delete
Sheets(1).Delete
lapok = Sheets.Count

Thank you for the help!

Solution

  • I'm no expert in VBA, but little google searching says error 9 is subscript out of range error.

    This likely is from Sheets(3). I also remember excel had 3 sheets open by default in those versions. Maybe that is not the case now, and causing the problem.