none
How to resolve 'subscript out of range' error on PasteSpecial that is inconsistent across a number of worksheets? RRS feed

  • Question

  • I need to convert all the formulas in a XLSM workbook into values.  I'm using the copy/pastespecial on the UsedRange in Excel 2010. 

    The code below always work on some of the worksheets in the file but not all. The code will run smoothly on a number of worksheets, then throw a "subscript out of range error" on the PasteSpecial line of code.  When the error happens, I can see that the pasting on that worksheet is actually done.    At time of the error, the correct result is visible, the entire UsedRange on the worksheet is still selected, but something is preventing the "cursor" from moving off that PasteSpecial line of code. It's almost like there are X steps within Microsoft's definition of the PasteSpecial action; even though the paste is effectively done, there are some remaining steps that cannot be finished and it throws out the subscript out of range error on that line. If I add an "on error resume next" before that line of code, it behaves completely the same (will not skip that error).

    After re-arranging the order of the worksheets, I found out that some with a particular design format will cause the error.  However, I cannot tell why it is the case. Both the worksheets that get executed and throw the error are unprotected, no merged cells, and have cell formatting, hyperlinks, named ranges and calculation formulas.  

    Is the worksheet layout affecting the PasteSpecial result?  If so, how do I generate a list of worksheet properties to compare the good ones and bad ones to see what could have cause this error?  

    Thank you!

    dim oldWB as workbook
    dim aTabOld as worksheet
    dim tabName
    
    ....
            
    For Each aTabOld In oldWB.Worksheets
                  tabName = aTabOld.Name
                  'MsgBox "now on worksheet: " & tabName
                  aTabOld.Activate
                  With ActiveSheet
                    .Hyperlinks.Delete
                    .UsedRange.Copy
                    .UsedRange.PasteSpecial xlPasteValues
                  End With
    Next aTabOld
    
    ....

    Monday, June 8, 2015 1:11 AM

Answers

  • I just wanted to let you know that I realized that there might be something corrupted with the XLSM file that caused the issue.  Once I save the file as XLSX, all worksheets are able to convert to values smoothly.  I'm going to stop pursuing this issue since I just need to adjust my work process a little bit.  Thank you for your response!
    • Marked as answer by fwang1888 Monday, June 8, 2015 7:08 AM
    Monday, June 8, 2015 7:07 AM

All replies

  • Hi,

    1. The first I got noticed is lacking of "Type" on the 3rd line.
        dim tabName *as string*

    2. I suppose the Range is not defined for ".Hyperlinks.Deleted", ".UsedRange.Copy"....
        between "With ActiveSheet" and "End With"
     
    If this wouln't help you, I'd like to see the real Excel file. Would you me the file?
    (by using such online storage as OneDrive)

    Regards,


    • Edited by Ashidacchi Monday, June 8, 2015 2:02 AM
    Monday, June 8, 2015 2:01 AM
  • Hi Ashidacchi,

    Thank you for the response.  I'm pretty sure the tabName and Hyperlinks are not the issues.  I added them for debugging purposes; even without those two lines, the code works the same.  As for the hyperlinks "UsedRange" is a property of the worksheet, which is automatically a range object.  I cannot share the file with you directly due to data confidentiality reasons.  Let me see if I can create a test file without sensitive information that will carry the same error.

    Thanks again!

    Monday, June 8, 2015 2:39 AM
  • Hi fwang1888,

    Thank you for the comment.  And I'm sorry about "UsedRange", I've just learned its meaning.
    But I'm not still sure as for ".HyperLinks.Delete".  It requires a Range or Selection, doesn't it?  

    I would be happy, if you could share a test file without sensitive information.

    Best regards,

    H. ASHIDA
    Monday, June 8, 2015 3:25 AM
  • Hyperlinks is an object of the worksheet, there for it is called from the ActiveSheet, not the range.  
    Monday, June 8, 2015 4:50 AM
  • I just wanted to let you know that I realized that there might be something corrupted with the XLSM file that caused the issue.  Once I save the file as XLSX, all worksheets are able to convert to values smoothly.  I'm going to stop pursuing this issue since I just need to adjust my work process a little bit.  Thank you for your response!
    • Marked as answer by fwang1888 Monday, June 8, 2015 7:08 AM
    Monday, June 8, 2015 7:07 AM
  • Hi,
    That's great.  I'm glad to hear that. Thank you for informing me.
    Have a happy day!
    • Edited by Ashidacchi Monday, June 8, 2015 7:28 AM
    Monday, June 8, 2015 7:24 AM