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

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

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

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

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

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

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