Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Location
    Australia
    Posts
    3

    Unanswered: Small problem with code

    Very new to this, could someone please let me know why I might be getting an error 'Run time error 1004' from the following code.
    Runs into drama towards the end where it tries to sort.
    Works when run on XP machine, no good on 2000 machine.

    Thanks in advance


    '
    Range("AK14").Select
    Selection.Copy
    Range("D29").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("AL14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D30").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("AM14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D31").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("AN14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D32").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("AO14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D33").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("AP14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("AQ14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D35").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("AR14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D36").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("AS14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D37").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("AT14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D38").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("B5:B14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B29").Select
    ActiveSheet.Paste
    Range("B2938").Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("D29"), Order1:=xlDescending, Header:=xlGuess _
    , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Range("B5").Select
    End Sub

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Your problem seems to be here (in bold):

    Range("B29: D38").Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("D29"), Order1:=xlDescending, Header:=xlGuess _
    , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    You are separating the "select" from the use of "selection" by the CutCopyMode command. Move that line above the Range.Select line and see what happens.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Nov 2003
    Location
    Australia
    Posts
    3
    Thanks for the suggestion Shades.
    Made the change, ran macro, still no go....., same error

    When using the debugger it highlights the following:

    Selection.Sort Key1:=Range("D29"), Order1:=xlDescending, Header:=xlGuess _
    , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal


    Any other suggestions?
    Last edited by MLS_72; 11-13-03 at 18:18.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, I think I see it now. Your range is B29 : D38

    Thus, your reference cell should be "B29" not "D29". Then change your OrderCustom:=3 (i.e. 3rd column over from selection, if you want to sort on column D). So that you would have this:


    Selection.Sort Key1:=Range("B29"), Order1:=xlDescending, Header:=xlGuess _
    , OrderCustom:=3, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal


    I'm not sure, but you may want to try this.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Just got to thinking more about this. When you make the selection, the reference becomes relative. Thus, "A1" becomes the new referent for the sort. Maybe try that change, rather than "B29".
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Nov 2003
    Location
    Australia
    Posts
    3
    Thanks again Shades.

    Been trying what you have suggested, no luck yet.

    Started looking at other areas.
    As I mentioned, this sheet works when run on XP but not on 2000.
    Sheet is protected.

    I changed the code to

    Selection.Sort Key1:=Range("D29"), Order1:=xlDescending, Header:=xlGuess _
    , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    and left sheet unprotected, woo hoo it works.
    But then I protect the sheet again and it stops on the same line of code again.
    This suggests that it is more to do with the level of protection, however with xl 2000 you cannot specifically allow for sort, or can you??

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    Glad you found the problem. Regarding protection, you could unprotect it at the beginning of your code and then re-protect at the end, after the sort. Just a thought. (I use XL 2002, and made the jump from 97 1 1/2 years ago, so don't know about 2000).
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    Works when run on XP machine, no good on 2000 machine.
    I just realized that I overlooked this little bit of info until a few hours ago.

    Either I'm getting old, or my eye sight is, or both!
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •