Help with multiple sorts, multiple sheets

  • Thread starter Thread starter Slow1911s
  • Start date Start date
S

Slow1911s

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
Click to expand...

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
 
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



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
Click to expand...
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
 
It's probably best to share the current version of your code and indicate which
line is causing the error.
 
It might even be good to include a short description of what the code is
supposed to do.
 
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
*
Click to expand...

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
 
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
 
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

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
 
Back
Top