Button Won't Work

[David127]

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]

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?