Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help with multiple sorts, multiple sheets
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Help with multiple sorts, multiple sheets

code


Private Sub CommandButton1_Click()
Dim ws2, ws3 As worksheet
Set sheets("sheet4") = ws2
Set sheets("Rep Summary") = ws3

ActiveWorkbook.RefreshAll

With ActiveSheet
Range("A104:B121").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range _
("c104:c105"), Unique:=False
End With

With ws2
Range("Rng_CR1").AdvancedFilter xlFilterCopy,
CopyToRange:=("Rng_CR2"), Unique:=True
Range("E2:E10000").Select
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

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

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

With ws3
Range("b12").Select
End With

End Sub


I run the code and I get a Run-time 424 error, Object Required. I did
paste this together from a couple of different recorded macros - have I
not defined something I should have?

TIA


--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile: http://www.excelforum.com/member.php...o&userid=31113
View this thread: http://www.excelforum.com/showthread...hreadid=537514

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,290
Default Help with multiple sorts, multiple sheets

This is a far as I got.

Change...
Dim ws2, ws3 As worksheet
Set sheets("sheet4") = ws2
Set sheets("Rep Summary") = ws3

To...
Dim ws2 as Excel.Worksheet
Dim ws3 as Excel.Worksheet
Set ws2 = Sheets("sheet4")
Set ws3 = Sheets("Rep Summary")
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Slow1911s" wrote in message
code
Private Sub CommandButton1_Click()
Dim ws2, ws3 As worksheet
Set sheets("sheet4") = ws2
Set sheets("Rep Summary") = ws3

ActiveWorkbook.RefreshAll

With ActiveSheet
Range("A104:B121").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range _
("c104:c105"), Unique:=False
End With

With ws2
Range("Rng_CR1").AdvancedFilter xlFilterCopy,
CopyToRange:=("Rng_CR2"), Unique:=True
Range("E2:E10000").Select
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

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

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

With ws3
Range("b12").Select
End With

End Sub
I run the code and I get a Run-time 424 error, Object Required. I did
paste this together from a couple of different recorded macros - have I
not defined something I should have?
TIA


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Help with multiple sorts, multiple sheets

Well, I'm getting a Run-time 1004 error. Thanks anyways!!!


--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile: http://www.excelforum.com/member.php...o&userid=31113
View this thread: http://www.excelforum.com/showthread...hreadid=537514

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Help with multiple sorts, multiple sheets
It's probably best to share the current version of your code and indicate which
line is causing the error.



Slow1911s wrote:

Well, I'm getting a Run-time 1004 error. Thanks anyways!!!

--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile: http://www.excelforum.com/member.php...o&userid=31113
View this thread: http://www.excelforum.com/showthread...hreadid=537514

--

Dave Peterson
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Help with multiple sorts, multiple sheets
It might even be good to include a short description of what the code is
supposed to do.

Slow1911s wrote:

Well, I'm getting a Run-time 1004 error. Thanks anyways!!!

--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile: http://www.excelforum.com/member.php...o&userid=31113
View this thread: http://www.excelforum.com/showthread...hreadid=537514

--

Dave Peterson
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Help with multiple sorts, multiple sheets

Here's the latest


Private Sub CommandButton1_Click()
Dim ws2 As Excel.Worksheet
Dim ws3 As Excel.Worksheet
Set ws2 = sheets("sheet4")
Set ws3 = sheets("Rep Summary")

ActiveWorkbook.RefreshAll

With ActiveSheet
Range("A104:B121").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range _
("c104:c105"), Unique:=False
End With

*With ws2
Range("Rng_CR1").AdvancedFilter xlFilterCopy,
CopyToRange:=("Rng_CR2"), Unique:=True
Range("E2:E10000").Select
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

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

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

With ws3
Range("b12").Select
End With

End Sub
*

The bold area is what is not excecuting. The code that is working is
the .RefreshAll and the first Sort (which happens to be on the same
page as the button). The remaining Sorts are on a different worksheet
in the workbook (sheet4).

Would a .activate for sheet4 and cancel screen updating be the trick?
The sorts that aren't working in this string were all recorded and
worked individually.

Thanks again,

DP


--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile: http://www.excelforum.com/member.php...o&userid=31113
View this thread: http://www.excelforum.com/showthread...hreadid=537514

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Help with multiple sorts, multiple sheets
Hard to tell where things are located, but here is a guess:

Private Sub CommandButton1_Click()
Dim ws2 As Excel.Worksheet
Dim ws3 As Excel.Worksheet
Set ws2 = sheets("sheet4")
Set ws3 = sheets("Rep Summary")

ActiveWorkbook.RefreshAll

With ActiveSheet
Range("A104:B121").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("c104:c105"), _
Unique:=False
End With

Range("Rng_CR1").AdvancedFilter
Action:=xlFilterCopy, _
CopyToRange:=Range("Rng_CR2"), _
Unique:=True

With ws2
.Range("E2:E10000").Sort _
Key1:=.Range("E2"), _
Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

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

.Range("j2:j10000").Sort _
Key1:=.Range("j2"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With

With ws3
.Select
Range("b12").Select
End With

End Sub

--
Regards,
Tom Ogilvy

"Slow1911s" wrote
in message ...

Here's the latest


Private Sub CommandButton1_Click()
Dim ws2 As Excel.Worksheet
Dim ws3 As Excel.Worksheet
Set ws2 = sheets("sheet4")
Set ws3 = sheets("Rep Summary")

ActiveWorkbook.RefreshAll

With ActiveSheet
Range("A104:B121").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range _
("c104:c105"), Unique:=False
End With

*With ws2
Range("Rng_CR1").AdvancedFilter xlFilterCopy,
CopyToRange:=("Rng_CR2"), Unique:=True
Range("E2:E10000").Select
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

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

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

With ws3
Range("b12").Select
End With

End Sub
*

The bold area is what is not excecuting. The code that is working is
the .RefreshAll and the first Sort (which happens to be on the same
page as the button). The remaining Sorts are on a different worksheet
in the workbook (sheet4).

Would a .activate for sheet4 and cancel screen updating be the trick?
The sorts that aren't working in this string were all recorded and
worked individually.

Thanks again,

DP


--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile:
http://www.excelforum.com/member.php...o&userid=31113
View this thread: http://www.excelforum.com/showthread...hreadid=537514



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Help with multiple sorts, multiple sheets

The first sort is on the same sheet as the command button - everythin
else is on "sheet4". The refresh and first sort works - then I get
1004 error. Here is what I have right now.


Private Sub CommandButton1_Click()
Dim ws2 As Excel.Worksheet
Dim ws3 As Excel.Worksheet
Set ws2 = sheets("sheet4")
Set ws3 = sheets("Rep Summary")

ActiveWorkbook.RefreshAll

Range("A104:B121").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("c104:c105"), _
Unique:=False


With ws2
.Range("a2:a10000").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("e2:e10000"), _
Unique:=True
.Range("E2:E10000").Sort _
Key1:=.Range("E2"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

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

.Range("j2:j10000").Sort _
Key1:=.Range("j2"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With


Range("b12").Select


End Sub


--
Slow1911
-----------------------------------------------------------------------
Slow1911s's Profile: http://www.excelforum.com/member.php...fo&userid=3111
View this thread: http://www.excelforum.com/showthread.php?threadid=53751

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Help with multiple sorts, multiple sheets
Private Sub CommandButton1_Click()
Dim ws2 As Excel.Worksheet
Dim ws3 As Excel.Worksheet
Set ws2 = sheets("sheet4")
Set ws3 = sheets("Rep Summary")

ActiveWorkbook.RefreshAll

With ActiveSheet
.Range("A104:B121").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=.Range("c104:c105"), _
Unique:=False
End With

Range("Rng_CR1").AdvancedFilter
Action:=xlFilterCopy, _
CopyToRange:=Range("Rng_CR2"), _
Unique:=True

With me
.Range("E2:E10000").Sort _
Key1:=.Range("E2"), _
Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
With ws2
.Range("i2:i10000").Sort _
Key1:=.Range("i2"), _
Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

.Range("j2:j10000").Sort _
Key1:=.Range("j2"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With

With ws3
.Select
ws3.Range("b12").Select
End With

End Sub


--
Regards,
Tom Ogilvy

"Slow1911s" wrote
in message ...

The first sort is on the same sheet as the command button - everything
else is on "sheet4". The refresh and first sort works - then I get a
1004 error. Here is what I have right now.


Private Sub CommandButton1_Click()
Dim ws2 As Excel.Worksheet
Dim ws3 As Excel.Worksheet
Set ws2 = sheets("sheet4")
Set ws3 = sheets("Rep Summary")

ActiveWorkbook.RefreshAll

Range("A104:B121").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("c104:c105"), _
Unique:=False


With ws2
.Range("a2:a10000").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("e2:e10000"), _
Unique:=True
.Range("E2:E10000").Sort _
Key1:=.Range("E2"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

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

.Range("j2:j10000").Sort _
Key1:=.Range("j2"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With


Range("b12").Select


End Sub



--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile:
http://www.excelforum.com/member.php...o&userid=31113
View this thread: http://www.excelforum.com/showthread...hreadid=537514



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
same range, multiple sheets, different sorts, help please! nikmasteed Excel Worksheet Functions 2 May 19th 06 04:30 PM
Custom sorts - Multiple Keys Rafi Excel Programming 3 February 15th 06 12:24 AM
Multiple Sorts Nigel Excel Programming 2 November 9th 04 02:23 PM
Multiple Sorts Ruan[_3_] Excel Programming 5 May 29th 04 02:50 AM
Macro for Multiple Sorts Dakota Excel Programming 5 February 4th 04 03:25 PM


All times are GMT +1. The time now is 03:34 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement