Sorting by Passing a range object, help!

sll810

Board Regular
Joined
Jun 29, 2007
Messages
86
I am trying to call a sub procedure multiple times to sort a sheet by different parameters and copy particular fields into another sheet.

The subprocedure looks like the code below:

Code:
Sub SortCopyTopFirms(dsheet As Excel.Worksheet, ssheet As Excel.Worksheet, sortrange As Excel.Range, xlfcpy As Excel.Range, firmpaste As Excel.Range, valcopy As Excel.Range, valpaste As Excel.Range, sortkey As String)

Dim Lrow As Long
Dim Lcol As Integer
Dim Lcell As Excel.Range
Dim xlapp As Excel.Application
Dim sheetrange As Excel.Range


Lrow = dsheet.Range("A" & dsheet.Rows.Count).End(xlUp).Row
Lcol = dsheet.Cells(1, dsheet.Columns.Count).End(xlToLeft).Column
Set Lcell = dsheet.Cells(Lrow, Lcol)
Set sheetrange = dsheet.Range(dsheet.Range("A1"), Lcell)

sheetrange.Sort key1:=sortrange, Order1:=sortkey, header:=xlYes
xlfcpy.Copy
firmpaste.PasteSpecial Paste:=xlPasteValues
valcopy.Copy
valpaste.PasteSpecial Paste:=xlPasteValues

End sub

I am calling the procedure with the call
Call SortCopyTopFirms(datasheet, summarysheet, E5sortrange, firmcopy, IE5firmpaste, E5valcopy, IE5valpaste, dsortstring)

I keep getting the error message "Sort Method of Range Class failed"

I have defined all of the variables to the appropriate types and set them properly. I have never tried to sort by a range object, I usually state xsheet.range("A1"), etc. In this case I can't send the sheet reference since it will be called multiple times.

Does anyone have any quick fixes!?

Thanks!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi sll810,

I can't tell for sure that this is a problem since I don't know how you assigned E5sortrange in the calling procedure, but I recommend that you pass sortrange as a text string rather than a Range object. This is because the key1 variant parameter will accept either a text string cell address or a Range object. By doing it as a text string you avoid the potential problem of having the worksheet object part of the range object reference different from the worksheet being sorted (specified by sheetrange) which would cause a sort error. If passed as a string you could just pass "$A$1".

Damon
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,160
Messages
5,768,530
Members
425,480
Latest member
br400821

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top