none
Excel VBA macro not working after upgrading to Microsoft Office 2013 from 2010 RRS feed

  • Question

  • I am having an issue with an excel VBA macro only after using 2013. Our end-users who are still on 2010 are nto experiencing this issue. I am trying to simply sort some columns:

      Columns("A:L").Sort key1:=Range("A2"), _
       order1:=xlAscending, Header:=xlYes

    And I then get the following error:

    Run-time error '1004':

    Sort method of Range class failed.

    Does anyone know why this happened? I've searched all over and have sen the same error in similar situations when moving from one verson of excel to a newer version, but I have not been able to fins a solution that works for me yet.

    Friday, November 14, 2014 7:12 PM

Answers

  • Re:  sorting in xl2013

    Leading off, I do not have xl2013, so I am guessing.

    Suggest you try specifying the workbook/worksheet for the sort area...
    '---
    Sub DoesntWork()
     With ThisWorkbook.Worksheets(2)                      '<<< adjust worksheet index
      .Columns("A:L").Sort key1:=.Range("A2"), _
                     order1:=xlAscending, Header:=xlYes  ' Note the dots
     End With
    End Sub
    '---
    Make sure the code module is in the same workbook as the sort range.
    Above code might (or might not) cure sort problem if created by implementation of Single Document Interface in xl2013.
    Fully referencing sheets/ranges is good coding practice anyway.
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, November 14, 2014 10:37 PM

All replies

  • Also, I am using Windows 7 Professional (64-bit).

    Thanks!

    Friday, November 14, 2014 7:13 PM
  • I wish I could put an attachment here, because it seems more to do with the data / structure of the spreadsheet then it actually does with the macro itself. The macro runs just fine on a blank spreadsheet.
    Friday, November 14, 2014 8:55 PM
  • So I have tried doing a couple things. First, I have verified that I am targeting the correct range by checking ht evaluue before the sort. Second, I have tried Initalizing all of the cells the best I could by using a 'clear all' commmand on all cells. I have tryied making sure that the spreadsheet is unprotected and that it is Active and slected before executing the code. I have cleared all data validation. None of these seem to be working.  
    Friday, November 14, 2014 9:25 PM
  • Re:  sorting in xl2013

    Leading off, I do not have xl2013, so I am guessing.

    Suggest you try specifying the workbook/worksheet for the sort area...
    '---
    Sub DoesntWork()
     With ThisWorkbook.Worksheets(2)                      '<<< adjust worksheet index
      .Columns("A:L").Sort key1:=.Range("A2"), _
                     order1:=xlAscending, Header:=xlYes  ' Note the dots
     End With
    End Sub
    '---
    Make sure the code module is in the same workbook as the sort range.
    Above code might (or might not) cure sort problem if created by implementation of Single Document Interface in xl2013.
    Fully referencing sheets/ranges is good coding practice anyway.
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, November 14, 2014 10:37 PM