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