Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default Button Won't Work

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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Button Won't Work

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro Does not Work on Button [email protected] Excel Worksheet Functions 4 March 28th 09 03:46 PM
Button suddenly does not work Man Utd New Users to Excel 2 October 20th 05 06:41 AM
Spin button in a work sheet - how do I make it work? [email protected] Excel Worksheet Functions 1 April 7th 05 08:43 PM
Button doesn't work SHIPP Excel Programming 1 December 11th 04 06:22 PM
Redo button does not work Dlemma2 Excel Discussion (Misc queries) 2 November 29th 04 08:28 PM


All times are GMT +1. The time now is 04:29 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"