Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I created a Macro that works fine but when I try to use the code with a
command button it fails to sort - "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort box isn't the same or blank". Private Sub cmdCopyList_MTN3_Click() Application.Goto Reference:="List_MTN2" Selection.Copy Application.Goto Reference:="List_MTN3" ActiveSheet.Paste Application.CutCopyMode = False Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Selection.Copy Application.Goto Reference:="List_MTN2" End Sub Thanks for your help! |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Those unqualified ranges refer to the sheet that owns the code--not the
activesheet. (There's a difference in behavior when the code is behind a worksheet and when the code is in a General module.) Private Sub cmdCopyList_MTN3_Click() Application.Goto Reference:="List_MTN2" Selection.Copy Application.Goto Reference:="List_MTN3" ActiveSheet.Paste Application.CutCopyMode = False Selection.Sort Key1:=activesheet.Range("A1"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Selection.Copy Application.Goto Reference:="List_MTN2" End Sub But maybe you could do it without the .goto's and selection. Private Sub cmdCopyList_MTN3_Click() dim List_Mtn2_Rng as range Dim list_mtn3_Rng as range set list_mtn2_rng = thisworkbook.names("List_Mtn2").referstorange set list_mtn3_rng = thisworkbook.names("List_Mtn3").referstorange 'Although, I find this syntax more self-documenting 'Set List_Mtn2_Rng = thisworkbook.worksheets("somesheetnamehere") _ ' .range("list_Mtn2") ' 'Set List_Mtn3_Rng = thisworkbook.worksheets("someothersheetnamehere") _ ' .range("list_Mtn3") list_mtn2_rng.copy _ destination:=list_mtn3_rng.cells(1) Application.CutCopyMode = False with list_mtn3_rng .cells.sort key1:=.columns(1), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End with End Sub (Untested, uncompiled--watch for typos!) I bet you know if your data has headers or not. Instead of letting excel guess, I'd specify that (xlyes or xlno--not xlguess). Why take a chance? David127 wrote: I created a Macro that works fine but when I try to use the code with a command button it fails to sort - "The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort box isn't the same or blank". Private Sub cmdCopyList_MTN3_Click() Application.Goto Reference:="List_MTN2" Selection.Copy Application.Goto Reference:="List_MTN3" ActiveSheet.Paste Application.CutCopyMode = False Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Selection.Copy Application.Goto Reference:="List_MTN2" End Sub Thanks for your help! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Macro Does not Work on Button | Excel Worksheet Functions | |||
Button suddenly does not work | New Users to Excel | |||
Spin button in a work sheet - how do I make it work? | Excel Worksheet Functions | |||
Button doesn't work | Excel Programming | |||
Redo button does not work | Excel Discussion (Misc queries) |