sort and copy selection to other worksheet

[Helmut]

I have the following: -BUT it doesn't sort Column R

' determine total range for sorting

Dim lr As Long, lc As Long '(in declarations)

lc = ActiveSheet.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
lr = ActiveSheet.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add _
Key:=Range(Cells(1, 1), Cells(lr, 1)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range(Cells(1, 1), Cells(lr, lc))
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

After this sorts Column R, I then need to copy values of Fields C,D,E for
the rows where value in R = #N/A to another WorkSheet.
Can you help?

 

[David Ritchie - Oz]

Hi Helmut

Based on what you supplied, this code might do the job:
Assumptions :
- sheet1 contains a number of columns A-R
- sheet2 is the destination sheet
- All data starts at row 1, column A

<-- snip start -->
'I have the following: -BUT it doesn't sort Column R

' determine total range for sorting

Dim lr As Long, lc As Long '(in declarations)
Sub testme()

lc = sheet1.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
lr = sheet1.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

Sheet1.Range("A1").Sort _
Key1:=Range(Cells(1, lc), Cells(lr, lc)), _
Order1:=xlAscending, _
DataOption1:=xlSortNormal, _
Header:=xlGuess

Dim eRng As Range

Set eRng = Range("A1").SpecialCells(xlCellTypeConstants, xlErrors)
Range(Cells(eRng.Row, 3), Cells(eRng.Row + eRng.Rows.Count - 1,
5)).Select
Selection.Copy Destination:=Worksheets("Sheet2").Range("E5")

End Sub
'After this sorts Column R, I then need to copy values of Fields C,D,E for
'the rows where value in R = #N/A to another WorkSheet.
'Can you help?


<-- snip end -->

Cheers David