locked
Copy and Paste Worksheets Between Files; Runtime Error Code 9 In Macro RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have a macro that develops a weekly report for customers broken out by department.  The report takes a data file and formats it into several department tabs and then puts an invoice "coversheet" on the file with a total for each department.  I've passed this report on to another person in my organization for their use and when they run the macro the portion of the code that copies the invoice template and pastes it into the weekly file hits a runtime error.  I haven't been able to recreate the error on my machine so my thought is that it might be a setting that the user needs to change but i'm not sure what it could be.  Any ideas?  The section of code that I'm having trouble with is listed below.  The section in bold is where we get the runtime error (code 9).

    ChDir "C:\Customer"
        ActiveWorkbook.SaveAs Filename:="C:\Customer\Weekly Workbook.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        Windows("Weekly invoice macro v2.xlsm").Activate
        Sheets("Invoice").Select
        Sheets("Invoice").Copy Before:=Workbooks("Weekly Workbook").Sheets(1)
        Sheets("Invoice").Select

    Monday, April 11, 2011 3:27 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    An error 9 (Subscript out of range) occurs when you attempt to access an item in a collection that doesn't exist. In your code, this would mean that either Workbooks("Weekly Workbook") does not exist or isn't recognized by Excel. You (or your user) may need to change the "Hide Extensions For Known File Types" Windows setting. Set the property to False. An alternative is to use the file extension ("xls", "xlsm", etc) in the workbook name. E.g., Workbooks("Weekly Workbook.xlsx").

    See http://www.cpearson.com/Excel/FileExtensions.aspx for a discussion of this Windows option and how it affects Excel.

     

     

    Chip Pearson
    Excel MVP (1998 - 2011)
    Pearson Software Consulting, LLC
    www.cpearson.com
    • Marked as answer by Calvin_Gao Monday, April 18, 2011 6:18 AM
    Monday, April 11, 2011 4:24 PM